Combine Multiple Parameters - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Combine Multiple Parameters

Dan Jackson

4 years ago

Sorry, this is gonna be the last one for a little while! Should be easy for someone who knows how...

I've created 8 Crosstab Queries (Pic 1) which are exported into an excel spreadsheet using a button (Code on button in Pic 2). The big man has now asked that he wants the option to specify a date range which I have successfully done thanks to Ricks teachings (Including declaring the parameters - Pic3)

The obvious issue - that's 16 times the date has to be entered (Start/end date per Crosstab x8). Can I do something in the buttons VBA to query the parameter once and then apply to all the queries as they run? I'm sure it's something done in the dev courses but not there quite yet (E25)!

Many Many Thanks

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
Put a couple of fields on your form for StartingDate and EndingDate then reference the form fields in each of your queries.

	Scott Axton @Reply 4 years ago
Yep Value From a Form if you forgot how.

	Dan Jackson OP @Reply 4 years ago
Cool! I was overthinking it!

	Dan Jackson OP @Reply 4 years ago
Almost there. I'm getting a field unrecognised error? >=[Forms]![MainSettingsMenuF]![Text_StartingDate] And <=[Forms]![MainSettingsMenuF]![Text_EndingDate]

	Dan Jackson OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
The first thing to do is check your spelling. Also, what line is highlighted when you click Debug?

Dan Jackson OP @Reply

4 years ago

Check. Form Name is MainSettingsMenuF and Field Names are Text_StartingDate and Text_EndingDate. Tried alternating between <= >= and the Between keyword. Also, while testing, i'm only using 1 query instead of all 8. Tried alternating to a different one

Pic 1 is the query using <= >=
Pic 2 is the query using Between Keyword
Pic 3 is The Code highlighted in debug mode
Pic 4 is when i tried an alternative query

thank You

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago
Just to say this is still an issue. Can't work out why Access doesn't recognise my field... cheers

	Dan Jackson OP @Reply 4 years ago
I've Tried Putting the Field in the Parameters box. I then get Runtime Error 3190 Too Many Fields. I'm out of ideas :( I tried Forms!MainSettingsMenuF!Text_StartingDate [Forms]![MainSettingsMenuF]![Text_StartingDate] [Text_StartingDate] Text_StartingDate

	Adam Schwanz @Reply 4 years ago
To verify, it worked before when you manually entered 16 dates? Try to CDATE it for kicks. >=CDATE([Forms]![MainSettingsMenuF]![Text_StartingDate]) and <=CDATE([Forms]![MainSettingsMenuF]![Text_EndingDate])

	Dan Jackson OP @Reply 4 years ago
Hey Adam, Thanks but no change :( Any other ideas?

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago
If it helps, I tried running the query from the navigation pane with the form open and still get the same error And to answer the question, yes, it works if i use parameter queries and type the date. Its actually 32 date entries because access requires the date twice (8 * Starting Date + 8 * Ending Date *2). I made him work it to the end, just for fun!

	Kevin Robertson @Reply 4 years ago
Try Compact and Repair then add the full field names to the Parameters Window again.

Dan Jackson OP @Reply

4 years ago

Ok, I cleared the two entries i had in the parameters window and ran C&R as advised
(I do it all the time anyway but doesn't hurt to try again)

Tried
Forms!MainMenuF!Text_StartingDate (and 2nd one for Ending Date)
and
[Forms]![MainSettingsMenuF]![Text_StartingDate]

Got Error 3190 Too Many Fields Defined

So i tried just the control names
Text_StartingDate and Text_EndingDate

Get same error above - 3070 ...does not recognise...

Also tried removing the CDATE from the VBA and setting the parameter value to short text instead of Date With Time to no effect

	Kevin Robertson @Reply 4 years ago
Here is an article on Runtime Error 3190 which may help. https://docs.microsoft.com/en-us/office/troubleshoot/access/too-many-fields-defined-error

	Dan Jackson OP @Reply 4 years ago
Thanks but the underlying table (Not the crosstab query i'm working on, but the table storing the data) has only 39 fields - it's the backbone of the database but nowhere near any limits.

	Dan Jackson OP @Reply 4 years ago
Any other ideas anyone?

	Kevin Robertson @Reply 4 years ago
Have you tried rebuilding the table? - Create a new table - Add all your fields - Save the table (any name will do) - Copy over all your data - Delete the original table - Rename the new table to the same name as the original Note: Make a good backup first. Then add the parameters to your query.

Dan Jackson OP @Reply

4 years ago

Cheers Kevin. I went one step better:

     - Created a brand new access file
     - Created a brand new table named same as the old one and manually typed fields
     - Imported the table from the BE, ran an Append Query to transfer the data and then deleted the BE copy
     - Recreated the Crosstab Query from scratch using the same details as the old one (Didn't set up an relationships, just used Ids)
     - Imported the required form

As a result, i have a clean database file
- with a new table, old records
- new CT query
- old form

Still getting the same error!!!

	Dan Jackson OP @Reply 4 years ago

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View