Query in code not working

[Johnny Bright]

I'm trying to do the following:

Set rstQueryData = db.OpenRecordset("Select * from qryAllParticipants" & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" & Me.txtEnd &
"#, dbOpenSnapshot")

I have of course declared all the required variables. I never know where to
put the #'s and the quotes etc. DateRecieved, txtbegin and end of course are
date fields. aryAllParticipants has as it's criteria the two text boxes on a
form and is an aggregate query with several Group by's and one Sum field.

All help greatly apprecitated!

 

[Smartin]

I'm trying to do the following:

Set rstQueryData = db.OpenRecordset("Select * from qryAllParticipants" & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" & Me.txtEnd &
"#, dbOpenSnapshot")

I have of course declared all the required variables. I never know where to
put the #'s and the quotes etc. DateRecieved, txtbegin and end of course are
date fields. aryAllParticipants has as it's criteria the two text boxes on a
form and is an aggregate query with several Group by's and one Sum field.

All help greatly apprecitated!

You do have a couple syntax mishaps in there. Here's a technique that
will help you for the rest of your coding days...

Create a variable to build the SQL string and examine it before you
execute the recordset to make sure you have the syntax correct:

Dim MySQL As String
MySQL = "Select * from qryAllParticipants " & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" &
Me.txtEnd & _
"#;"
Debug.Print MySQL ' does it look correct?
Stop
Set rstQueryData = db.OpenRecordset(MySQL, dbOpenSnapshot)

Note you omitted a space after "from qryAllParticipants" (or before
"Where DateRecieved) and misplaced the closing quotes for the SQL
string.

 

[Johnny Bright]

Hi there!

Thanks! Ok, I tried:

mySQL = "Select * from qryAllParticipants " & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" & Me.txtEnd & "#;"

Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)

but I'm still getting a 3075 runtime error,
Syntax error (Missing Operator) in expression 'Date Recieved >=## and <=##'

Help! , i'm sure I'm just missing some dumb comma or something somewhere!

Thanks again!

 

[Benjamins]

hi johnny

Try this
Change this part of code

mySQL = "Select * from qryAllParticipants " & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" & Me.txtEnd & "#;"
To

mySQL = "Select * from qryAllParticipants " & _
"Where DateRecieved >= #" & Me.txtBegin & "# and DateRecieved <= #" & Me.txtEnd & "#;"

Hi there!

Thanks! Ok, I tried:

mySQL = "Select * from qryAllParticipants " & _
"Where DateRecieved >= #" & Me.txtBegin & "# and <= #" & Me.txtEnd & "#;"

Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)

but I'm still getting a 3075 runtime error,
Syntax error (Missing Operator) in expression 'Date Recieved >=## and <=##'

Help! , i'm sure I'm just missing some dumb comma or something somewhere!

Thanks again!

On Nov 14, 5:08 pm, Johnny Bright
<[email protected]> wrote:

[quoted text clipped - 29 lines]

"Where DateRecieved) and misplaced the closing quotes for the SQL
string.

 

[Dale Fye]

Actually, I think it should read:

mySQL = "Select * from qryAllParticipants " _
& "Where DateRecieved >= #" & Me.txtBegin & "# " _
& " AND DateRecieved<= #" & Me.txtEnd & "#"

HTH
Dale

 

[Johnny Bright]

I really appreciate everybody's help on this but I'm still getting a syntax
error!

I also just realised I need to add another field, this one is a text field
based on a combo box. My code now looks like this:

mySQL = "Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateRecieved >= #" & Me.txtBegin & "# " _
& " AND DateRecieved<= #" & Me.txtEnd & "#"

However, the yellow highlighting goes to this line:

Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot) is there a
problem here?

What is the advantage with the variable vs using this code?

Set rstQueryData = db.OpenRecordset("Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateRecieved >= #" & Me.txtBegin & "# " _
& " AND DateRecieved<= #" & Me.txtEnd & "#", dbOpenSnapshot)

Thanks again so much!

 

[Douglas J. Steele]

Your error message 'Date Recieved >=## and <=##' implies that there's
nothing in the controls txtBegin and txtEnd.

The advantage of using the variable is that you can easily add a debugging
step

Debug.Print mySQL

in your code after you've set the variable and look at what's actually in
it.

 

[Johnny Bright]

Hi Doug!

Thanks for your help!

The debug window shows me exactly what I'm looking for, the right province
and the values from my text boxes, however, now I'm getting an error 3061,
too few parameters, expected 4 which makes no sense as there are only 3
parameters in the underlying query! Arrrrrrrg!

 

[Douglas J. Steele]

Make sure you haven't mistyped any of the names. If Access sees a name it
can't figure out, it assumes it's a parameter.

 

[Johnny Bright]

Well, I did have Received spelled wrong so I thought that would fix it but
it's still not working! Still getting too few parameters. Does this code
look right?

mySQL = "Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateReceived >= #" & Me.txtBegin & "# " _
& "AND DateReceived<= #" & Me.txtEnd & "#"

Debug.Print mySQL
Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)

 

[Douglas J. Steele]

What gets printed to the Immediate window by the Debug.Print statement?

What's the SQL for qryAllParticipants?

 

[Johnny Bright]

The Immediate window shows:
Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

the sql for the query is:

SELECT Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Sum(Donation.Amount) AS Total, Runner.FirstName & " " &
Runner.LastName AS Name, Runner.Address1 & " " & Runner.Address2 & " " &
Runner.City AS Address, Runner.Email, Runner.PhoneNumber
FROM RunSite INNER JOIN (Runner INNER JOIN Donation ON Runner.RunnerID =
Donation.RunnerID) ON RunSite.RunSiteID = Runner.RunSiteID
WHERE (((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And
(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))
GROUP BY Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Runner.FirstName & " " & Runner.LastName, Runner.Address1 &
" " & Runner.Address2 & " " & Runner.City, Runner.Email, Runner.PhoneNumber
HAVING (((Runner.ProvinceStateID)=[Forms]![frmLook]![cboProv]));

Thanks! I really appreciate your help!

 

[Douglas J. Steele]

I don't see DateReceived as a field in qryAllParticipants. The only fields
that are returned are ProvinceStateID, RunSiteID, City, CompanyID, Total,
Name, Address, Email and PhoneNumber (remember, if it's not in the SELECT
part, you can't refer to it.)

Also, the query is looking for [Forms]![frmLook]![cboProv]. Is the form
open?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Immediate window shows:
Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

the sql for the query is:

SELECT Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Sum(Donation.Amount) AS Total, Runner.FirstName & " " &
Runner.LastName AS Name, Runner.Address1 & " " & Runner.Address2 & " " &
Runner.City AS Address, Runner.Email, Runner.PhoneNumber
FROM RunSite INNER JOIN (Runner INNER JOIN Donation ON Runner.RunnerID =
Donation.RunnerID) ON RunSite.RunSiteID = Runner.RunSiteID
WHERE (((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And
(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))
GROUP BY Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Runner.FirstName & " " & Runner.LastName,
Runner.Address1 &
" " & Runner.Address2 & " " & Runner.City, Runner.Email,
Runner.PhoneNumber
HAVING (((Runner.ProvinceStateID)=[Forms]![frmLook]![cboProv]));

Thanks! I really appreciate your help!

What gets printed to the Immediate window by the Debug.Print statement?

What's the SQL for qryAllParticipants?

 

[Johnny Bright]

DateReceived is part of the where clause in the query. > > WHERE
(((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And

(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))

The query is activated by a button on frmLook so the form is definitely
open! I suppose if this stuff was easy, everybody would be doing it!
Any ideas?

I don't see DateReceived as a field in qryAllParticipants. The only fields
that are returned are ProvinceStateID, RunSiteID, City, CompanyID, Total,
Name, Address, Email and PhoneNumber (remember, if it's not in the SELECT
part, you can't refer to it.)

Also, the query is looking for [Forms]![frmLook]![cboProv]. Is the form
open?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Immediate window shows:
Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

the sql for the query is:

SELECT Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Sum(Donation.Amount) AS Total, Runner.FirstName & " " &
Runner.LastName AS Name, Runner.Address1 & " " & Runner.Address2 & " " &
Runner.City AS Address, Runner.Email, Runner.PhoneNumber
FROM RunSite INNER JOIN (Runner INNER JOIN Donation ON Runner.RunnerID =
Donation.RunnerID) ON RunSite.RunSiteID = Runner.RunSiteID
WHERE (((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And
(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))
GROUP BY Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Runner.FirstName & " " & Runner.LastName,
Runner.Address1 &
" " & Runner.Address2 & " " & Runner.City, Runner.Email,
Runner.PhoneNumber
HAVING (((Runner.ProvinceStateID)=[Forms]![frmLook]![cboProv]));

Thanks! I really appreciate your help!

What gets printed to the Immediate window by the Debug.Print statement?

What's the SQL for qryAllParticipants?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Well, I did have Received spelled wrong so I thought that would fix it
but
it's still not working! Still getting too few parameters. Does this
code
look right?

mySQL = "Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateReceived >= #" & Me.txtBegin & "# " _
& "AND DateReceived<= #" & Me.txtEnd & "#"

Debug.Print mySQL
Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)

 

[Douglas J. Steele]

Your query is

Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

That's querying qryAllParticipants, and the only fields that exist in that
query are ProvinceStateID, RunSiteID, City, CompanyID, Total, Name, Address,
Email and PhoneNumber. If you need to be able to refer to DateReceived in
this query, you need to add Donation.DateReceived to the list of fields that
qryAllParticipants is returning. That fact that qryAllParticipants refers to
DateReceived doesn't make any difference: it must be in the list of fields
between SELECT and FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

DateReceived is part of the where clause in the query. > > WHERE
(((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And

(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))

The query is activated by a button on frmLook so the form is definitely
open! I suppose if this stuff was easy, everybody would be doing it!
Any ideas?

I don't see DateReceived as a field in qryAllParticipants. The only
fields
that are returned are ProvinceStateID, RunSiteID, City, CompanyID, Total,
Name, Address, Email and PhoneNumber (remember, if it's not in the SELECT
part, you can't refer to it.)

Also, the query is looking for [Forms]![frmLook]![cboProv]. Is the form
open?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The Immediate window shows:
Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

the sql for the query is:

SELECT Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Sum(Donation.Amount) AS Total, Runner.FirstName & " "
&
Runner.LastName AS Name, Runner.Address1 & " " & Runner.Address2 & " "
&
Runner.City AS Address, Runner.Email, Runner.PhoneNumber
FROM RunSite INNER JOIN (Runner INNER JOIN Donation ON Runner.RunnerID
=
Donation.RunnerID) ON RunSite.RunSiteID = Runner.RunSiteID
WHERE (((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And
(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))
GROUP BY Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Runner.FirstName & " " & Runner.LastName,
Runner.Address1 &
" " & Runner.Address2 & " " & Runner.City, Runner.Email,
Runner.PhoneNumber
HAVING (((Runner.ProvinceStateID)=[Forms]![frmLook]![cboProv]));

Thanks! I really appreciate your help!

:

What gets printed to the Immediate window by the Debug.Print
statement?

What's the SQL for qryAllParticipants?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Well, I did have Received spelled wrong so I thought that would fix
it
but
it's still not working! Still getting too few parameters. Does
this
code
look right?

mySQL = "Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateReceived >= #" & Me.txtBegin & "# " _
& "AND DateReceived<= #" & Me.txtEnd & "#"

Debug.Print mySQL
Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)

 

[Johnny Bright]

Thanks again! The problem is that as soon as I add DateRecieved to the query
itself I get crazy results when I run the query. Rather than 445 records, i
get over 2,000 because of the joins. I'll have to look at the query again.

Thanks for all your help on this!

Your query is

Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

That's querying qryAllParticipants, and the only fields that exist in that
query are ProvinceStateID, RunSiteID, City, CompanyID, Total, Name, Address,
Email and PhoneNumber. If you need to be able to refer to DateReceived in
this query, you need to add Donation.DateReceived to the list of fields that
qryAllParticipants is returning. That fact that qryAllParticipants refers to
DateReceived doesn't make any difference: it must be in the list of fields
between SELECT and FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

DateReceived is part of the where clause in the query. > > WHERE
(((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And

(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))

The query is activated by a button on frmLook so the form is definitely
open! I suppose if this stuff was easy, everybody would be doing it!
Any ideas?

I don't see DateReceived as a field in qryAllParticipants. The only
fields
that are returned are ProvinceStateID, RunSiteID, City, CompanyID, Total,
Name, Address, Email and PhoneNumber (remember, if it's not in the SELECT
part, you can't refer to it.)

Also, the query is looking for [Forms]![frmLook]![cboProv]. Is the form
open?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The Immediate window shows:
Select * from qryAllParticipants Where ProvinceStateID = 'CAAB' AND
DateReceived >= #1/1/2007# AND DateReceived<= #11/30/2007#

the sql for the query is:

SELECT Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Sum(Donation.Amount) AS Total, Runner.FirstName & " "
&
Runner.LastName AS Name, Runner.Address1 & " " & Runner.Address2 & " "
&
Runner.City AS Address, Runner.Email, Runner.PhoneNumber
FROM RunSite INNER JOIN (Runner INNER JOIN Donation ON Runner.RunnerID
=
Donation.RunnerID) ON RunSite.RunSiteID = Runner.RunSiteID
WHERE (((Donation.DateReceived)>=[Forms]![frmLook]![txtBegin] And
(Donation.DateReceived)<=[Forms]![frmLook]![txtEnd]))
GROUP BY Runner.ProvinceStateID, RunSite.RunSiteID, RunSite.City,
Runner.CompanyID, Runner.FirstName & " " & Runner.LastName,
Runner.Address1 &
" " & Runner.Address2 & " " & Runner.City, Runner.Email,
Runner.PhoneNumber
HAVING (((Runner.ProvinceStateID)=[Forms]![frmLook]![cboProv]));

Thanks! I really appreciate your help!

:

What gets printed to the Immediate window by the Debug.Print
statement?

What's the SQL for qryAllParticipants?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Well, I did have Received spelled wrong so I thought that would fix
it
but
it's still not working! Still getting too few parameters. Does
this
code
look right?

mySQL = "Select * from qryAllParticipants " _
& "Where ProvinceStateID = '" & Me.cboProv & "' " _
& "AND DateReceived >= #" & Me.txtBegin & "# " _
& "AND DateReceived<= #" & Me.txtEnd & "#"

Debug.Print mySQL
Set rstQueryData = db.OpenRecordset(mySQL, dbOpenSnapshot)