Question:
I have a database with a field set to Date/Time.My project has a variable that is of type Date.In a text box it displays 04/21/97. I cannot get the SQL statement to recognize it so that I can select those records with that date. I can use the format #4/21/97#. Help!
Dim SQLsource As String Dim stringTempDate As String stringTempDate = dateWeekEdit Text2.Text = stringTempDate SQLsource = “SELECT * FROM Timecard” ‘ SQLsource = “SELECT * FROM Timecard WHERE Empl_Number = ” _ & EmployeeNumber & ” AND Wk_Ending = ” & stringTempDate ‘ The above SQL statement has trouble using any date string ‘ to check against the Wk_Ending field which is set to Date/Time ‘ format. datTimecard.DatabaseName = App.Path & dbTimeCard datTimecard.RecordSource = SQLsource datTimecard.Refresh
Answer:
You’re on the right track using the pound signs; however, I would try itlike this:
SQLsource = “SELECT * FROM Timecard WHERE Empl_Number = ” _ & EmployeeNumber & ” AND Wk_Ending = #” & stringTempDate & “#”Let stringTempDate just be the date. Make sure that the Wk_Ending field isactually a Date/Time field and not something else. The pound sign trickonly works on real date/time fields.