VBA Date Query to Find Records - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

VBA Date Query to Find Records

Sam Rideout

4 years ago

Hey Everyone,
I have a problem I am stuck on and I am hoping you can steer me in the right direction.

I am trying to use two text boxes on my form to get dates using the datePicker and then to find records in my table that are between the two dates using VBA.  I have no problem Pulling all record data from the table and my issue is in trying to limit the data pulled with the appropriate WHERE string.

I have tried a variety of things but I am unable to find the records.  I have the text boxes formatted for General Dates.  I have tried converting the text box strings to date using CDate to no avail.  I have tried to implement a SQL query to pull only the records between the two dates converting the date strings to numbers using #s to no avail.  And I have tried to compare the dates to the DB date using recordsource data also to no avail.

I am trying to do all this without using a query because I want my user to use the date picker rather than typing in the date manually with a parameter.  I am looking for as few keyboard entries as possible.  Any ideas would be much appreciated!

Sam

	Alex Hedley @Reply 4 years ago
Please share some code you've tried

	Alex Hedley @Reply 4 years ago
It's prob a date format issue If you hardcode a date `WHERE MyDate>#12/31/2012#` does it work?

Sam Rideout OP @Reply

4 years ago

sqlString = "SELECT * " & _
            " FROM OrderT " & _
            " WHERE CDate(DeliveryStartDate) >= #" & Format(CDate(txtFirstDate), "mm/dd/yyyy") & "# " & _
            " AND CDate(DeliveryStartDate) < #" & Format(CDate(txtLastDate), "mm/dd/yyyy") & "#;"

and.....

        While Not rs.EOF
            theDate = Format(rs!DeliveryStartDate, "mm/dd/yyyy")

            If theDate >= Format(DateValue(txtFirstDate), "mm/dd/yyyy") Then
                fileStream.WriteLine rs!OrderID & "," & rs!DeliveryStartDate & "," & rs!NumCords & "," & rs!NumCords * rs!CordPrice
            Else
                MsgBox ("No Joy!!")
            End If

            rs.MoveNext
        Wend

	Sam Rideout OP @Reply 4 years ago
This code returns no records: sqlString = "SELECT * " & _ " FROM OrderT " & _ " WHERE DeliveryStartDate >= #9/1/2022# AND DeliveryStartDate < #9/30/2022#"

	Alex Hedley @Reply 4 years ago
This won't work `WHERE CDate(DeliveryStartDate)` You're supposed to be saying `Field = Value`

	Alex Hedley @Reply 4 years ago
If you try it in the Query editor, hard coding the dates does it work? If so then change to SQL view and share that

	Sam Rideout OP @Reply 4 years ago
Thanks Alex.... This code uses my field DeliveryStartDate: sqlString = "SELECT * " & _ " FROM OrderT " & _ " WHERE DeliveryStartDate >= #9/1/2022# AND DeliveryStartDate < #9/30/2022#" and returns no records

	Sam Rideout OP @Reply 4 years ago
Thanks Alex I got it now....

	Alex Hedley @Reply 4 years ago
Great, what fixed it?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View