Why is SQL search for Date not finding recordset

[kleaverjr]

This really puzzles me.

I have a date field in a table. The date that is being searched for changes so the SQL language uses a variable for the date. When I put in an actual date in the SQL language, Access finds the record, but when I replace the date with a formatted variable (and the format matches what is in the table) no recordset is found. So here is the code. I hope someone can tell me what I am doing wrong. I have searched and searched and every solution I find online, does not work. Thanks.

ShowWeekend = Format(ShowWeekend, "M/D/YYYY"

sqlFindCurrentShow = " SELECT * FROM Show_SalesSummaryLog WHERE Show_Date_Start = " & ShowDateString 

Set dbFindCurrentShow = CurrentDb
Set rsRindCurrentShow = dbFindCurrentShow.OpenRecordset(sqlFindCurrentShow)

And ShowWeekend= 9/2/2023

The above code does not find the record. But the code BELOW does:

sqlFindCurrentShow = " SELECT * FROM Show_SalesSummaryLog WHERE Show_Date_Start = 9/2/2023" 

Set dbFindCurrentShow = CurrentDb
Set rsRindCurrentShow = dbFindCurrentShow.OpenRecordset(sqlFindCurrentShow)

What am I misunderstanding.

And thanks again for all who have been helping me with this program. I have been programming in BASIC since I was 5 years old when my grandfather started teaching me on his own kit built Apple II computer, and eventually learning Quick Basic then Visual Basic, and now VBA (since I really don't like VB.net) When it comes to VBA I am "self taught" along with those who have been helping me on forums like this so though I understand the Logic, things like this stump me and drive me crazy as logically it should work but it isn't. So thanks for your patience!

Ken L

[orange]

Ken,

It would be helpful if you showed all the code involved.
Hopefully you have dimmed dbFindCurrentShow?
What is ShowDateString in your first example?

[kleaverjr]

I deleted all references to ShowDateString (forgot to update the code when I typed it up)

Here's the rest of the relevant code.

Dim ShowWeekend As date
dim dbFindCurrentShow as DAO.Database
Dim rsFindCurrentShow as DAO.Recordset
dim sqlFindCurrentShow as string 

ShowWeekend = Format(ShowWeekend, "M/D/YYYY"

sqlFindCurrentShow = " SELECT * FROM Show_SalesSummaryLog WHERE Show_Date_Start = " & ShowWeekend 

Set dbFindCurrentShow = CurrentDb
Set rsRindCurrentShow = dbFindCurrentShow.OpenRecordset(sqlFindCurrentShow)

The actual program is on a different computer that is not connected to the internet, and transfering the file via USB Drive is a pain, so I am manually typing the code into the forums to find solutions to the problem.

And there is a whole bunch of code that is used to determine the value of ShowWeekend. But that is irrelevant to the issue because the value of ShowWeekend in this case IS 9/2/2023 which matches what is in the Record. When I put 9/2/2023 into the WHERE clause, it finds the record, but when it's a value from a variable it doesn't. WHY?!


Thanks.

Ken L.

[pbaldy]

In the code above you never set ShowWeekend to a date value, you only try to format that value. If it's set elsewhere, you may be dealing with two different variables with the same name, and you don't have Option Explicit set.

[orange]

Ken,

This statement is missing a closing ")"
ShowWeekend = Format(ShowWeekend, "M/D/YYYY" <=====this results in a string data type NOT a Date

However, a Date data type requires # separators.

For example,

Dim mdate as Date
mdate =#8/29/23#

You do not have a value assigned to ShowWeekend??

When you Dim a variable as a Date, then it will require the # before and after when you use it.

sqlFindCurrentShow = " SELECT * FROM Show_SalesSummaryLog WHERE Show_Date_Start = #" & ShowWeekend & "#"

[kleaverjr]

ShowWeekend is set in an entirely separate part. But even when I had set ShowWeekend = "9/02/2023" though the SQLString still showed the text in the string correctly, it could not find the date. When I inserted the # characters though, that solved the problem. I was not aware that I had to insert those characters when searching for a date. Thanks for the assist.

Ken L

[Welshgasman]

I used to use this constant in some of my DBs

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

[pbaldy]

I was going to mention the delimiters but you said this worked:

sqlFindCurrentShow = " SELECT * FROM Show_SalesSummaryLog WHERE Show_Date_Start = 9/2/2023"

which surprised me.