Hi everybody,

I'm trying to consolidate 3 pop up parameters in one form where the user will enter the values in text boxes.

I have a date parameter, a country parameter and an amount parameter where I enter any value I want.

My SQL for these parameters is:

- WHERE Product.Date = [Date:]

- WHERE Product.Country Like "*" & [Search by any Country: ] & "*"

- WHERE Product.Amount Between [Amount > X] And [Amount < Y]

I succeeded in coding the first parameter which is the date in VBA after creating the form with the appropriate text and check boxes.

The VBA code for the date parameter is:

sWHERE = sWHERE & " AND Product.Date = " & _
"#" & Format$(txtDate, "mm/dd/yyyy") & "#"

and it returns all the records for the date I specify in txtDate box in my form

Could anyone help me finishing the code for the two other parameters (country and amount) that my form would return whatever the user would type in the text boxes txtCountry and txtAmount

sWHERE = sWHERE & " AND Product.Country......txtCountry...

sWHERE = sWHERE & " AND Product.Amount......txtAmount...

Thanks for the help, I'm coding in Access 2000

Hi Skan,

This will work if you have a little validation code to ensure that all your parameter fields are filled in. I'd recommend handling your 'Amount' parameter using two input text boxes: txtLowAmount and txtHighAmount, or something to that effect.

sWhere = ""
sWhere = "[Date] Like " & "#" & _
         Format$(Me!txtDate, "mm/dd/yyyy") & "#"
sWhere = sWhere & " AND [Country] Like " & _
         Chr$(34) & "*" & Me!txtCountry & "*" & Chr$(34)
sWhere = sWhere & " AND [Amount] >= " & Me!txtLowAmount
sWhere = sWhere & " AND [Amount] <= " & Me!txtHighAmount

A more robust multi-parameter search would give the user the option of filling in either 1 parameter field, all 4, or any combination there of. That gets a little involved, but it's well worth figuring out. Let me know if you're interested and I could e-mail you a simple example using your criteria. Its probably a bit much to post.

HTH,

Bob

Hi Skan,

Actually, you can get rid of the Format funcion around date. I just copied that in from what you had posted but it isn't necessary:

   sWhere = ""
    sWhere = "[Date] Like " & "#" & Me!txtDate & "#"
    sWhere = sWhere & " AND [Country] Like " & Chr$(34) & "*" & Me!txtCountry & "*" & Chr$(34)
    sWhere = sWhere & " AND [Amount] >= " & Me!txtLowAmount
    sWhere = sWhere & " AND [Amount] <= " & Me!txtHighAmount

Bob

"A more robust multi-parameter search would give the user the option of filling in either 1 parameter field, all 4, or any combination there of. That gets a little involved, but it's well worth figuring out. Let me know if you're interested and I could e-mail you a simple example using your criteria. Its probably a bit much to post."

Hi Bob,

I'm interested in finding out how this works. I have a single table with ac_type, ac_registry, maint_type, date_performed, location and remarks. I am trying to create a form that lets me fill in either one of the parameters or all and then a report will display those matching my criteria. I tried following some steps stated in this forum but to no avail. Hope you can help me.

Thank you so much!

Regards,
Tet

What steps did you follow?

mmcdonal

Look it up at: http://wrox.books24x7.com