Pear-shaped SQL statement

[hokiewalrus]

This is (for me) a fairly complicated SQL statement and it has a problem that I can't track down. Access, however, only gives me the generic "Too few parameters (expected=1)" error.

PaymentSource = "SELECT [tTransPayments].[PaymentAmount] FROM [tTransPayments] " & _
                    "INNER JOIN [tTransHeader] ON [tTransHeader].[TransactionNumber] = [tTransPayments].[TransactionNumber] " & _
                    "WHERE ([tTransHeader].[TransactionDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#) " & _
                    "AND ([tTransPayments].[PaymentType] = " & Blank & ") " & _
                    "AND ([tTransHeader].[TransactionStatus] = 'Posted');"
    Set Payments = dbs.OpenRecordset(PaymentSource)

What I know: StartDate, EndDate, and Blank are all variables being passed to the function and they are all correct. I just need all the posted transactions within the given date range where the payment type is what I've specified.

Thanks in advance

 

[Alc]

Is Blank a number?
If not, that may be the problem area.

 

[hokiewalrus]

Ah yes, the single quotes! Of course it's something silly, thanks so much, it works now.

 

[MSAccessRookie]

This is (for me) a fairly complicated SQL statement and it has a problem that I can't track down. Access, however, only gives me the generic "Too few parameters (expected=1)" error.

PaymentSource = "SELECT [tTransPayments].[PaymentAmount] FROM [tTransPayments] " & _
                    "INNER JOIN [tTransHeader] ON [tTransHeader].[TransactionNumber] = [tTransPayments].[TransactionNumber] " & _
                    "WHERE ([tTransHeader].[TransactionDate] BETWEEN #" & StartDate & "# AND #" & EndDate & "#) " & _
                    "AND ([tTransPayments].[PaymentType] = " & Blank & ") " & _
                    "AND ([tTransHeader].[TransactionStatus] = 'Posted');"
 
[B][COLOR=red]    MsgBox PaymentSource[/COLOR][/B]
[B][COLOR=red]    Stop[/COLOR][/B]
 
    Set Payments = dbs.OpenRecordset(PaymentSource)

What I know: StartDate, EndDate, and Blank are all variables being passed to the function and they are all correct. I just need all the posted transactions within the given date range where the payment type is what I've specified.

Thanks in advance

Add the code in red and see if the message matches what you expect it should be. If it does not, It should give you a clue as to what to do next.

 

[Alc]

Ah yes, the single quotes! Of course it's something silly, thanks so much, it works now.

Glad to help.

 

[Banana]

Add the code in red and see if the message matches what you expect it should be. If it does not, It should give you a clue as to what to do next.

Just a thought-

If we forget to omit the Stop statement, it could be very embarrassing if it makes into the production when the user is happily working away and all of sudden taken to VBE for no apparent reason.

For that reason I prefer to use breakpoint, which accomplish the same thing as Stop but are temporary in that if you save and close up the .mdb then re-open it, it's all gone. One can set breakpoints by click on the gray margin to left of the code windows.