This SQL statement should be dead simple (1 Viewer)

[Salient]

sSql = "select * from Books where JournalMonth='" & txtMonth & "'"
[/quot]

Ends up with

select * from Books where JournalMonth='Aug-97'

It's not returning any data even though there are records matching the selection criteria

Anyone know if spaces in a cell could cause this issue?

 

[Uncle Gizmo]

Date part function

When you use SQL in a query grid, you can use natural dates, however when you copy that code and use it in an SQL statement as a string, then the dates must be formatted correctly for SQL. In other words SQL is an American language and the dates must be formatted in the American format of month, day, year. Also the dates should be enclosed in the number character, the hash, #.

I produced a short video demonstrating various problems with dates here:

 

[Salient]

When you use SQL in a query grid, you can use natural dates, however when you copy that code and use it in an SQL statement as a string, then the dates must be formatted correctly for SQL. In other words SQL is an American language and the dates must be formatted in the American format of month, day, year. Also the dates should be enclosed in the number character, the hash, #.

I produced a short video demonstrating various problems with dates here:

Thanks for the answer Gizmo but it's actually a text rather thna date field

 

[Salient]

Okay sussed it, SQL doesn't like the "-" in the selection data. A bit of use of the string function and a Like clause and away we go Drat, damn, who designed this database!

 

[Uncle Gizmo]

Ah! I see ...

I presume you've tried it without the apostrophes round it?

 

[Salient]

Ah! I see ...

I presume you've tried it without the apostrophes round it?

Oops nope .... will give it a go on the second testing round

 

[Guus2005]

sSql = "select * from Books where JournalMonth=#" & txtMonth & "#"

should give you:

select * from Books where JournalMonth=#Aug-97#

#Aug-97# is a date.

If this doesn't work try

sSql = "select * from Books where CDate(JournalMonth)=#" & txtMonth & "#"