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 August 13th, 2003, 03:16 PM
Registered User
 
Join Date: Aug 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parameters from SQL to VBA

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



 
Old August 14th, 2003, 11:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old August 14th, 2003, 11:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old October 23rd, 2007, 10:47 PM
tet tet is offline
Registered User
 
Join Date: Oct 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

"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

 
Old October 25th, 2007, 06:53 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What steps did you follow?

mmcdonal

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
run query via vba with parameters The_Vicar VBScript 0 April 21st, 2008 06:38 AM
sql parameters webbie5 Classic ASP Databases 5 April 12th, 2006 05:21 AM
Reading hardware parameters from VBA code out5yder Access VBA 5 October 6th, 2005 02:24 AM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM
Excel2002: Passing parameters from IE to VBA HappyAlex Excel VBA 0 April 6th, 2004 11:26 AM





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