By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Passing variable to SQL query, no records returned.

P: 23
I'm using Access 97 SR-2 on Windows XP to develop an Inventory style database that has some functionality, but having a problem making my query dynamic. I have a switchboard style form which I choose from a selection of 3 command buttons to select a Due Date for record reviews, choices being 30 days, 60 days, and 120 days. When I click on one of the buttons, A public variable, mqryDueDate, is set, for example, to today's date + 30 days, and is of 'Date' type in the declarations. This variable is then used in my query to pull all records which have a due date within the next 30 days. Simple stuff I thought, but the way I have my code and SQL statement, I have no records returned. If I have the query prompt me to manualy enter a date, the correct records ARE returned, but it's when trying to pass the variable data through to the query, nothing is returned. Using the command button's OnClick event, the following (relevant) VBA code is run...

Expand|Select|Wrap|Line Numbers
  1.  
  2. mqryDueDate = Date + 30
  3.  
  4. DoCmd.OpenReport "rptDueDate", acViewPreview
  5.  
  6.  
The report's record source is a query called qryDueDate, and contains a SQL statement which is supposed to capture the value of variable mqryDueDate and return records based on..

Expand|Select|Wrap|Line Numbers
  1.  
  2. WHERE (((AllInstructions.DueDate)< [Forms]![frmReportByDueDate]!mqryDueDate));
  3.  
  4.  
However, no results are returned, and no error is received.

I have had a look at various posts regarding this kind of situation, but no resolve was found. I am suspecting it's that my version of Access isn't able to do this, or I've typed in the WHERE statement wrong.

Any help would be appreciated, and if I've forgotten to provide any information (as per posting guidelines), I do apologise.

thanks...
Mar 10 '10 #1

✓ answered by TheSmileyCoder

Should be as simple as this:
Expand|Select|Wrap|Line Numbers
  1.   'Set Date
  2.     Me.txtqryDueDate=Date+30
  3.   'Open report
  4.     DoCmd.OpenReport "rptDueDate", acViewPreview 
And I was wrong about the # , they are not needed if your using a textbox.
Expand|Select|Wrap|Line Numbers
  1. WHERE (((AllInstructions.DueDate)< [Forms]![frmReportByDueDate]![txtqryDueDate]));

Share this Question
Share on Google+
9 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Is mqryDueDate bound? I suspect maybe if its unbound, access doesn't know that your storing a date type in the field, and formats it as a string instead? (this is just a guess)

You could try:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & ));
Mar 10 '10 #2

P: 23
Thanks for quick reply, however, I'm getting a syntax error (missing operator) when I cut and paste the amended SQL statement. I'm sure this is just something silly that I'm missing

Also, the variable is not bound. It's purely a public variable that is used and set only when the OnClick event occurs, and then used for query.

The OnClick vba code is simple only the two lines previously quoted (but obviously with start and end sub and error handler).

If it helps, here is the full SQL statement

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW AllInstructions.DocumentNumber, AllInstructions.IssueNumber, AllInstructions.DisciplineID,  AllInstructions.DocumentStatusID, AllInstructions.DueDate
  2. FROM AllInstructions
  3. WHERE (((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & )); 
  4.  
and error...

Syntax error (missing operator) in query expressions '(((AllInstructions.DueDate)< & "#" & [Forms]![frmReportByDueDate]!mqryDueDate & "#" & ))'.
thanks...
Attached Images
File Type: jpg error.jpg (17.8 KB, 289 views)
Mar 10 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Is mqryDueDate a variable or a textbox on the form? I dont think the SQL engine can see the variables, even if public (but im unsure, I have never tried)
Mar 10 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Well tried setting a public variable for the form, and referencing that from my query, but no such luck. I didn't get any error msg, just no returned records.
I created a unbound textbox (visible=false) and set that using code, then opening query, referencing the Hidden textbox in my criteria, and that worked just fine.
Mar 10 '10 #5

P: 23
It's a variable. The form has 3 command buttons for selecting a Due Date (and 1 to exit). When the command button is clicked, the OnClick event runs the VBA code to set mqryDueDate to the selected date.

It would make sense to me (disappointingly) that it doesn't work if the SQL engine can't see the variable, and I suppose I'll have to come up with another method of creating a dynamic query for reports.

I may even just have users enter the date manually, not ideal for my purposes, but whatever works will have to do.

thanks...
Mar 10 '10 #6

P: 23
I'll try using the unbound text box just now and post my result.

thanks...
Mar 10 '10 #7

P: 23
I created a unbound textbox (visible=false) and set that using code, then opening query, referencing the Hidden textbox in my criteria, and that worked just fine.
I've tried doing this, but it's not working. I do know, however, that it's because I'm not sure how to do this.

If I call the textbox txtqryDueDate, can you provide the code to do this please, and what the SQL query would then be?

thanks...
Mar 10 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Should be as simple as this:
Expand|Select|Wrap|Line Numbers
  1.   'Set Date
  2.     Me.txtqryDueDate=Date+30
  3.   'Open report
  4.     DoCmd.OpenReport "rptDueDate", acViewPreview 
And I was wrong about the # , they are not needed if your using a textbox.
Expand|Select|Wrap|Line Numbers
  1. WHERE (((AllInstructions.DueDate)< [Forms]![frmReportByDueDate]![txtqryDueDate]));
Mar 10 '10 #9

P: 23
Thanks, Smiley. That works ideally!!! :)

When I tried it first time, I missed the "me." when setting the value! yeah, silly, I know. :doh:

thanks...
Mar 10 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.