Query: Update Criteria

[vince1818]

Hi All -

Happy New Year!

I just wanted to check if there's anyway I can create a Macro or any suggestions on how to update a Criteria in the query window without going through the design view. Maybe I can create a form where our staff can update the criteria in a sort of a form, button using a macro hat will update the query in the backend. I may not make sense, but I will be happy to explain it further.
I hope to get some help soon. Thank you!

Vince

 

[pbaldy]

Most of us let the user enter the criteria on a form and let the query get it there:

Between [Forms]![FormName]![txtFromDate] And [Forms]![FormName]![txtToDate]

 

[vince1818]

Thanks Paul! That really helps. Follow up question, after I run the query, can I append it to an existing table using the form I used to enter the parameters?


And also, on a side question, what is the best way to delete duplicate records in an existing table?

Thank you and hoping to hear from you soon.

 

[pbaldy]

Well, an append query can use the same criteria. You could execute one from your form.

You might want to search or start a new thread on the duplicates. I've never had to do much of that, so not sure of best methods.

 

[vince1818]

Thanks Paul. I was able to follow use the same criteria you provided.

One last question for now, Im trying to put a "last modified date" in the form, but i dont know how, any suggestions? Thanks again

 

[pbaldy]

No problem. I'm not sure what you're trying to do with that date. It can be added to the append query if that's what you're after.

 

[vince1818]

I attached the screenshot of my Parameter form. Im trying to set the last modified date whenever an append query is completed.

Maybe there's other way to do it thanks!

 

[pbaldy]

Well, the button that runs the query can set it:

Me.TextboxName = Date() ' or Now() if you want date and time

 

[vince1818]

Hi Paul,

Im not sure where can I set Me.TextboxName = Date()
do I need to put it in the append query button properties?

 

[pbaldy]

What is behind the button now? If VBA code, you'd add that line. If a macro, you'd add an action, either SetValue or SetProperty, I think it varies with version. I don't use macros so not sure.

 

[vince1818]

Hi Paul,

Quick question on the parameters. Previously, I was able to create a parameter to run and append a query using a date range. Now, Im trying to add an additional parameter on a different field (Job No.) criteria [Forms]![Parameter Form]![JobNoQuery]. However, when I run the query, the Job No. field is always required. It wont run the query if Iam only using the date range parameters. Is there a way I can run the query using both parameters or use only Job No. or just the date range. I hope I make sense. Thank you.

 

[pbaldy]

One way to make the job optional:

http://access.mvps.org/access/queries/qry0001.htm