Problem with Date Query!

[slaman]

This is the query, where FromDate and ToDate are fields on a search form.

SELECT Businesses.[Expiry Date], Businesses.[Business Name], Corporations.[Corporation Name], Corporations.LawyerID, Corporations.FirmID FROM Corporations INNER JOIN Businesses ON Corporations.[Corporation ID] = Businesses.[Corporation ID] WHERE (((Businesses.[Expiry Date]) Between [FromDate] And [ToDate]));

I get the error: You must have a SQL statement to use the DoCmd.RunSQL command... Hrmm... Odd, I used the query wizard to generate this... Any ideas?

 

[JoeMiller]

DoCmd.RunSQL = "SELECT Businesses.[Expiry Date], Businesses.[Business Name], Corporations.[Corporation Name], Corporations.LawyerID, Corporations.FirmID FROM Corporations INNER JOIN Businesses ON Corporations.[Corporation ID] = Businesses.[Corporation ID] WHERE (((Businesses.[Expiry Date]) Between " & Me.[FromDate] & " And " & Me.[ToDate] & "));"

The query wizard generated the proper select statement, but when you added the criteria, it blew it up because you didn't reference the control properly by making it part of the string. If you're only going to do the RunSQL command then that is what I suggest. If you are going to modify an existing query's definition then you should use this:

SELECT Businesses.[Expiry Date], Businesses.[Business Name], Corporations.[Corporation Name], Corporations.LawyerID, Corporations.FirmID FROM Corporations INNER JOIN Businesses ON Corporations.[Corporation ID] = Businesses.[Corporation ID] WHERE (((Businesses.[Expiry Date]) Between [Forms]![MyFormName]![FromDate] And [Forms]![MyFormName]![ToDate]));

HTH Joe Miller
joe.miller@flotech.net