SQL syntax problems

[jamessl]

Can anyone point me to some kind of reference which points out the ground rules for the SQL syntax in an SQL string in Visual basic. It obviously differs from that you see in the SQL view of a standard Access Query grid.

The Access help function only has limited examples it would seem. My current problem is trying to resolve an SQL string which calculates two Between statements as follows -

Forms![Bookings]![Bookings - Subform 1]![Start Time].RowSource = "SELECT [Start Time] FROM Periods WHERE [Start Time] NOT BETWEEN " & Periods(1, 1) & " AND " & Periods(1, 2) & " AND [Start Time] NOT BETWEEN " & Periods(2, 1) & " AND " & Periods(2, 2) & ""

Currently this is giving me a syntax error, but if I have only one Between statement it works fine, ie

Forms![Bookings]![Bookings - Subform 1]![Start Time].RowSource = "SELECT [Start Time] FROM Periods WHERE [Start Time] NOT BETWEEN " & Periods(1, 1) & " AND " & Periods(1, 2) & ""

Must be some missing brackets or something but I have tried many variations.

help appreciated!

 

[RiverGuy]

Are you typing this all out on 1 line? Kinda seems that [Start Time] may be considered one word on how you have it displayed here.

But that should not give you a syntax error, just a bad field name.

 

[Steve101]

What exactly is the syntax error? Publish it in your next post. It usually provides a good indication of the problem.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[r937]

totally guessing, but try changing

WHERE A NOT BETWEEN B AND C
AND X NOT BETWEEN Y AND Z

to

WHERE NOT A BETWEEN B AND C
AND NOT X BETWEEN Y AND Z

the only other thing i can see is maybe an extra doublequote at the end of the string

rudy

http://rudy.ca/

 

[Steve101]

Rudy,

Nothing wrong generally with the initial syntax:

WHERE A NOT BETWEEN B AND C
AND X NOT BETWEEN Y AND Z

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[jamessl]

Ok,

River Guy, it is not all on one line - thought I would put it into one line in my example on this thread to get rid of some of the quotation marks and '&'s.

So in my code it looks like -

Forms![Bookings]![Bookings - Subform 1]![Start Time].RowSource = "SELECT [Start Time] FROM Periods WHERE [Start Time] NOT BETWEEN" _
& " " & Periods(1, 1) & " AND " & Periods(1, 2) & " AND [Start Time] NOT BETWEEN" _
& " " & Periods(2, 1) & " AND " & Periods(2, 2) & ""


The syntax error is (reproduced exactly) -

syntax error (missing operator) in query expression '[Start Time] NOT BETWEEN AND AND [Start Time] NOT BETWEEN AND'.

any other ideas? I'm pretty sure the word order etc is fine but am just missing something small.

 

[Steve101]

James,

The error message is indicating that the Periods(n,m) function is returning a value of a Null string or an empty string.

Basically it would seem that the problem with your SQL is not the SQL syntax, but the evaluation of the Period(n,m) function. Its not returning a date value, as you expect, but is essentially returning nothing, which consequently makes the SQL invalid.

Perhaps if you publish the code behind the Periods(n,m) function - we'll be able to see why its returning no value.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[jamessl]

Thanks, indeed looks like the reason I am having problems -

I am not sure exactly why the array is null because it is supposed to be full, but I will attempt to get to the bottom of it - time to do some debugging.

If I am still having problems after looking into the code you may well hear from me at this thread!

thanks.