Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 28th, 2005, 04:02 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default too few parameters expected n

this is my case: i have a query and a form and a VBA code that excutes when a button is clicked on the form and exports the data from the query into a text file.
the problem is that on the form i have to input a start and end date so that the query results will be limited by the period given.
the question is: how do i tell my vba code the dates that i have entered through the form so that it will add them to the sql query in the "where" clause?
this is the part of my code that is causing trouble:

  Dim db As Database
  Dim rs As Recordset
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("myquery") <-----this is where it's going wrong and asking for the parameters.
i use the recordset to hold the results of the query while exporting to the file.
Hope you can help me because it's really important
Thank you



Reply With Quote
  #2 (permalink)  
Old April 28th, 2005, 07:14 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   You did not mention the controls you are using for the start and stop dates. I will assume that you are using data pickers called dteStart and dteEnd:

'=====

Dim dteStart As String
Dim dteEnd As String

...

dteStart = Me.dteStart
dteEnd = Me.dteEnd

...

stSQL = "SELECT * FROM ... WHERE [DateField] BETWEEN " & "#" & dteStart & "#" & " AND " & "#" & dteEnd & "#"

'=====

Anyway, you get the idea. The posters here should be able to correct my syntax.

HTH

mmcdonal
Reply With Quote
  #3 (permalink)  
Old April 28th, 2005, 07:47 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

In your query, for the date field you are checking, reference the form fields using:

[Forms]![yourformname]![textfieldcontrolname]

E.g. If you are reporting on a single date from your table (call it "SampleToDate") and your form name is "SampleReportStart" and your from date is "txtFromDate" and your to date is "txtToDate" specify this criteria for the SampleToDate in your "myquery":

Between [Forms]![SampleReportStart]![txtFromDate] and [Forms]![SampleReportStart]![txtToDate]

Be careful of the "Between" or for that matter be careful of using "<=" and ">=". If any of the values in SampleToDate include time, records will not be selected. That happens because "txtToDate" will be interpretted as "mm/dd/yyyy 00:00:00 am".

There are several ways around this:
+ Have your users specify the time (or add the time for the users)

+ Format the date field of the table in your query:

Format([SampleToDate],"mm/dd/yyyy") as CheckDate

+ alter the criteria to:

Between [Forms]![TextOutput]![txtFromDate] and (CDate([Forms]![SampleReportStart]![txtToDate]) + 1)

Note: the last solution will pick up SampleToDate values for one day greater than the user specified if the values in SampleToDate are date only.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
Reply With Quote
  #4 (permalink)  
Old April 28th, 2005, 07:49 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i'm using text boxes for the dates. in my code i don't have an stSQL that you suggested in your reply. my sql is in the query which i only reference in the code by db.OpenRecordSet("myQuery")
i altered my where clause so that it looks like this
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between
 " & "#" & StartDate & "#" And " & "#" & EndDate & "#" ));

but i got "syntax error missing operator in query expression
so i changed it to

WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between
"#" & StartDate & "#" And "#" & EndDate & "#" ));

and i'm back to the originol error message "too few parameters...."
any ideas how to fix this?
Quote:
quote:Originally posted by mmcdonal
 Hi,

You did not mention the controls you are using for the start and stop dates. I will assume that you are using data pickers called dteStart and dteEnd:

'=====

Dim dteStart As String
Dim dteEnd As String

...

dteStart = Me.dteStart
dteEnd = Me.dteEnd

...

stSQL = "SELECT * FROM ... WHERE [DateField] BETWEEN " & "#" & dteStart & "#" & " AND " & "#" & dteEnd & "#"

'=====

Anyway, you get the idea. The posters here should be able to correct my syntax.

HTH

mmcdonal
Reply With Quote
  #5 (permalink)  
Old April 28th, 2005, 07:58 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

I do. Read my post!

Just change "StartDate" and "EndDate" as I described.

Be careful of your Format to "yyyymmdd". If the users don't enter that format, your WHERE won't work. Access figures out that you're working with dates.

And don't forget about that TIME issue. TxnDate implies there might be a time in the value.
Reply With Quote
  #6 (permalink)  
Old April 28th, 2005, 08:30 AM
Authorized User
 
Join Date: Apr 2005
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

so you suggest that i add nothing to my VBA code and just change my where clause in the sql of the query.
this is what i did. my sql looks like this now
WHERE (((Format([Check]![TxnDate],"yyyymmdd")) Between [Forms]![IntroForm]![FromDate] And [Forms]![IntroForm]![ToDate]));
but it still doesn't work and i get the same error message....
it looks like i'm missing something here


Quote:
quote:Originally posted by rjweers
 I do. Read my post!

Just change "StartDate" and "EndDate" as I described.

Be careful of your Format to "yyyymmdd". If the users don't enter that format, your WHERE won't work. Access figures out that you're working with dates.

And don't forget about that TIME issue. TxnDate implies there might be a time in the value.
Reply With Quote
  #7 (permalink)  
Old April 29th, 2005, 06:33 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

(Please don't quote entire messages anymore. Thx.)

Two possible problems.

One, your form is not open in View mode when you're running the query. If the form isn't open [Forms]!... can't access the information in the form.

Two, your query doesn't work to begin with. Try making sure the query works without criteria. Is [Check] then name of a table in your query? Is [TxnDate] the name of a field in the [Check] table? Are you properly joining [Check] to other tables you're viewing?

Perhaps you should try posting the the SQL statement from the query so we can make sure the whole syntax is correct.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
update stt- Too few parameters. Expected 1. s_sanks Classic ASP Databases 0 March 7th, 2008 08:03 AM
too few parameters expected 1 jim11 Access VBA 1 December 7th, 2005 03:41 PM
Too few parameters. 1 expected !!! chacquard Access VBA 2 June 21st, 2005 08:00 AM
Error "Too Few Parameters - Expected 1" timmaher Access VBA 2 August 31st, 2004 04:04 AM
Too few parameters. Expected 1. harry_barry Classic ASP Databases 2 May 26th, 2004 04:12 PM



All times are GMT -4. The time now is 05:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.