Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 September 25th, 2003, 10:18 AM
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default create query through a form



I want to create a query based on input from many fields on a form, where not each of these fields must be filled in.
My questions:
1. How to make such a query?
2. What if I have, say 2 fields on the form, A and B where field A="10" and field B is not filled in. How to make a query that looks for records where field A="10" and field B can be anything (that's why field B wasn't filled in on the form).

Afterwards I want to use this query as recordsource for a new table.

Any help is welcome

 
Old September 25th, 2003, 10:31 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Go here (Duane Hookom's website):

http://www.invisibleinc.com/download.cfm?filFilID=7

And download the Corp Tech Demos. It has an example of Query By Form.

Regards,

Beth M
 
Old September 25th, 2003, 11:09 AM
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Nice example but,

There you can select al the fields because every field is a record of a table and so you can loop through every record of that table.
In my application the different fields are text boxes and combos randomly arranged on a form. So how can I cycle though all the fields?

any suggestions?

 
Old September 25th, 2003, 11:05 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Build your criteria by checking for not isnull value in your controls and build the criteria string as you go like in the following example:

Dim WhereStr As String
Dim SqlString As String
Dim db As Database

Set db = CurrentDb
SqlString = "Select From RecordSource Where "


If Not IsNull(f![MyDateField]) Then
   WhereStr = WhereStr & "DateField <= #" & f![MyDateField] & "#"
End If

If Not IsNull(f![NumField]) Then
   WhereStr = WhereStr & " AND NumField = " & f![NumField]
End If

'continue building your string of criteria
'put it all together
SqlString = SqlString & WhereStr
set rs = db.openrecordset(SqlString, dbopendynaset)

'close recordset when finished
rs.close
set rs = nothing
db.close
set db = nothing
 
Old September 26th, 2003, 06:53 AM
Registered User
 
Join Date: Sep 2003
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks,

that's what I needed!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Create a field in a query Vision G Access 1 July 26th, 2006 03:33 PM
How to Create Join Script for a Query ....... ZArrinPour SQL Server 2000 0 June 25th, 2006 11:48 AM
create query at runtime vipinpaliwal1980 Access 1 April 24th, 2006 06:27 AM
Can anyone create this query for me??????????? avats SQL Language 1 August 23rd, 2005 01:07 PM
Create a DLL for inserting a query into jaideep VB Components 1 May 16th, 2005 06:49 AM





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