Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 28th, 2005, 04:02 AM
Authorized User
 
Join Date: Apr 2005
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



 
Old April 28th, 2005, 07:14 AM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old April 28th, 2005, 07:47 AM
Friend of Wrox
 
Join Date: Nov 2004
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
 
Old April 28th, 2005, 07:49 AM
Authorized User
 
Join Date: Apr 2005
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
 
Old April 28th, 2005, 07:58 AM
Friend of Wrox
 
Join Date: Nov 2004
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.
 
Old April 28th, 2005, 08:30 AM
Authorized User
 
Join Date: Apr 2005
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.
 
Old April 29th, 2005, 06:33 AM
Friend of Wrox
 
Join Date: Nov 2004
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.





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





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