Formatting SQL in OpenRecordset expression

J

Joan

Hi,

I am wanting to open a recordset using a SQL statement but am having
problems with the formatting of the expression. Could someone tell me what
I am doing wrong? This is what I have:

Dim DB as Database
Dim rstInvoices As DAO. Recordset

Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(((Invoices.[Invoice Number]) Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And ((Invoices.Type)="INV"))" ,
dbOpenDynaset


I get a message that it expects the end of statement when Access gets to the
second " in front of INV. How should I write this instead?

I want to loop through the Invoice records between [FromInvoice] and
[ToInvoice] on the frmPrintReportsDialog form and print out each invoice
separately so that the page number information is correct for each
customer's Invoice Statement. The control source for the page information
on the report is: ="Page " & [Page] & " of " & [Pages]

Joan
 
G

Gary Miller

Joan,

Two things jump out to me. You seem to have an uneven number
of right and left parenthesis. Also you are using double
quotes around INV and you should only use singles for text
values when you are building the string. Try this...

Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices
WHERE
(Invoices.[Invoice Number] Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And (Invoices.Type=
'INV')" ,
dbOpenDynaset

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
J

Joan

Hi Gary,

I tried using the SQL you sent in my code and when I press "Enter" I get a
Compile Error message that states : "Expected : end of statement" The
highlight ends after this part of the statement: ...'INV')".

The SQL I used was:
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(Invoices.[Invoice Number] Between Forms!frmPrintReportsDialog2!FromInvoice
And Forms!frmPrintReportsDialog2!ToInvoice) And (Invoices.Type = 'INV')",
dbOpenDynaset

Do you have any further recommendations?
Joan


Gary Miller said:
Joan,

Two things jump out to me. You seem to have an uneven number
of right and left parenthesis. Also you are using double
quotes around INV and you should only use singles for text
values when you are building the string. Try this...

Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices
WHERE
(Invoices.[Invoice Number] Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And (Invoices.Type=
'INV')" ,
dbOpenDynaset

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Joan said:
Hi,

I am wanting to open a recordset using a SQL statement but am having
problems with the formatting of the expression. Could someone tell me what
I am doing wrong? This is what I have:

Dim DB as Database
Dim rstInvoices As DAO. Recordset

Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(((Invoices.[Invoice Number]) Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And ((Invoices.Type)="INV"))" ,
dbOpenDynaset


I get a message that it expects the end of statement when Access gets to the
second " in front of INV. How should I write this instead?

I want to loop through the Invoice records between [FromInvoice] and
[ToInvoice] on the frmPrintReportsDialog form and print out each invoice
separately so that the page number information is correct for each
customer's Invoice Statement. The control source for the page information
on the report is: ="Page " & [Page] & " of " & [Pages]

Joan
Click to expand...
Click to expand...
 
G

Gary Miller

Here you go. Forgot to concantenate the form variables in.
This compiles on mine...

Set rstInvoices = db.OpenRecordset("SELECT * FROM Invoices
WHERE(Invoices.[Invoice Number] Between " &
Forms!frmPrintReportsDialog2!FromInvoice & " And " &
Forms!frmPrintReportsDialog2!ToInvoice & ") And
(Invoices.Type = 'INV')", dbOpenDynaset)

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Joan said:
Hi Gary,

I tried using the SQL you sent in my code and when I press "Enter" I get a
Compile Error message that states : "Expected : end of statement" The
highlight ends after this part of the statement: ....'INV')".

The SQL I used was:
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(Invoices.[Invoice Number] Between Forms!frmPrintReportsDialog2!FromInvoice
And Forms!frmPrintReportsDialog2!ToInvoice) And (Invoices.Type = 'INV')",
dbOpenDynaset

Do you have any further recommendations?
Joan


Joan,

Two things jump out to me. You seem to have an uneven number
of right and left parenthesis. Also you are using double
quotes around INV and you should only use singles for text
values when you are building the string. Try this...

Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices
WHERE
(Invoices.[Invoice Number] Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And (Invoices.Type=
'INV')" ,
dbOpenDynaset

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Joan said:
Hi,

I am wanting to open a recordset using a SQL statement
Click to expand...
but
am having
problems with the formatting of the expression. Could someone tell me what
I am doing wrong? This is what I have:

Dim DB as Database
Dim rstInvoices As DAO. Recordset

Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(((Invoices.[Invoice Number]) Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And ((Invoices.Type)="INV"))" ,
dbOpenDynaset


I get a message that it expects the end of statement
Click to expand...
when
Access gets to the
second " in front of INV. How should I write this instead?

I want to loop through the Invoice records between [FromInvoice] and
[ToInvoice] on the frmPrintReportsDialog form and
Click to expand...
print
out each invoice
separately so that the page number information is
Click to expand...
correct
for each
customer's Invoice Statement. The control source for
Click to expand...
the
page information
on the report is: ="Page " & [Page] & " of " & [Pages]

Joan
Click to expand...
Click to expand...
Click to expand...
 
J

Joan

Thanks Gary. That syntax worked great!
So appreciated.

Joan


Gary Miller said:
Here you go. Forgot to concantenate the form variables in.
This compiles on mine...

Set rstInvoices = db.OpenRecordset("SELECT * FROM Invoices
WHERE(Invoices.[Invoice Number] Between " &
Forms!frmPrintReportsDialog2!FromInvoice & " And " &
Forms!frmPrintReportsDialog2!ToInvoice & ") And
(Invoices.Type = 'INV')", dbOpenDynaset)

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Joan said:
Hi Gary,

I tried using the SQL you sent in my code and when I press "Enter" I get a
Compile Error message that states : "Expected : end of statement" The
highlight ends after this part of the statement: ...'INV')".

The SQL I used was:
Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices WHERE
(Invoices.[Invoice Number] Between Forms!frmPrintReportsDialog2!FromInvoice
And Forms!frmPrintReportsDialog2!ToInvoice) And (Invoices.Type = 'INV')",
dbOpenDynaset

Do you have any further recommendations?
Joan


Joan,

Two things jump out to me. You seem to have an uneven number
of right and left parenthesis. Also you are using double
quotes around INV and you should only use singles for text
values when you are building the string. Try this...

Set rstInvoices = DB.OpenRecordset "SELECT * FROM Invoices
WHERE
(Invoices.[Invoice Number] Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And (Invoices.Type=
'INV')" ,
dbOpenDynaset

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Hi,

I am wanting to open a recordset using a SQL statement but
am having
problems with the formatting of the expression. Could
someone tell me what
I am doing wrong? This is what I have:

Dim DB as Database
Dim rstInvoices As DAO. Recordset

Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset "SELECT * FROM
Invoices WHERE
(((Invoices.[Invoice Number]) Between
Forms!frmPrintReportsDialog!FromInvoice And
Forms!frmPrintReportsDialog!ToInvoice) And
((Invoices.Type)="INV"))" ,
dbOpenDynaset


I get a message that it expects the end of statement when
Access gets to the
second " in front of INV. How should I write this
instead?

I want to loop through the Invoice records between
[FromInvoice] and
[ToInvoice] on the frmPrintReportsDialog form and print
out each invoice
separately so that the page number information is correct
for each
customer's Invoice Statement. The control source for the
page information
on the report is: ="Page " & [Page] & " of " & [Pages]

Joan
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop through records not working 2
Please help with Update query 11
My event procedure doesn't work like it did before? 5
OpenRecordset, AddNew won't add a record? 5
Too Few Parameters in OpenRecordset 3
Batch reports and page numbers 4
Running SQL statements in VBA 4
what causes an error that says: "Too few Parameters. Expected 1." 3

Top