leave blank

  • Thread starter Thread starter cmichaud
  • Start date Start date
C

cmichaud

I am having a problem with a query that is using a form to get its
criteria. there are several comboboxes on the form and i want to be
able to leave them blank to search all for that category.

i am using this sort of format
[Forms]![frmResourceAttributes]![ResourceCombo] Or
[Forms]![frmResourceAttributes]![ResourceCombo] Is Null

the problem is that the query returns nothing.

even if there is not a record for the individual for a certain category
i dont want this to disqualify the record from coming up.

any ideas on how i can get this to work??
 
Oops. To use your data...


Like "*" & [Forms]![frmResourceAttributes]![ResourceCombo] & "*" And Like
"*"
 
Rick

I changed it as you said and now it does bring up the records.
However...it doesnt allow me to sort. It just returns all.

I am trying to get it so if i leave a box blank it will return all.
But if it has a selection it will only return those with that value or
that had been left blank by the user.


I have 4 comboboxes. Lets say Status, Position, Club, and project.

So if the user leaves all blank i want it to return every record.
If they select active(under status) i want them to return all users
that are active (or those that have a blank record here).
Etc.
Is this possible.? New here.
 
Then put the criteria for each one on the same line in the query grid. This
will create an "and" condition.
 
still not working. could it be cause i also have in the query
emailaddress, which i have set in the criteria to is not null.

here is a sample of the sql to show the ands
AND ((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID])
AND ((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID])) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID])) OR
(((tblPersonalInfo.Email) Is Not Null) AND

or maybe i have set the command button onclick even wrong. i just have
an opening the query.
Private Sub Command12_Click()
On Error GoTo Err_Command12_ClickDim stDocName As String
stDocName = "MyEmailAddresses"
DoCmd.OpenQuery stDocName, acNormal, acEdit

I dont know what else to do. Right now it brings up the two members
with email. But wont let me sort on any of the other fields. Wont let
me bring up every active person with email, but leave the inactives
out.
 
Well, I don't see the text that I sent you in your query. Where is the
"Like" and the "& "*""?

Under each column, put the criteria I sent you. Do so on the same ROW in
the criteria sectionof the query grid.

Under PositionsID, put...
Like "*" & [Forms]![frmContact]![PositionID] & "*" And Like "*"

Under ProjectID, put...
Like "*" & [Forms]![frmContact]![ProjectID] & "*" And Like "*"

etc.


You are saying, pull all records where "PositionID" is what I entered unless
I leave the criteria blank, then pull all records *AND* pull all records
where "ProjectID" is what I entered unless I leave the criteria blank, etc.
 
hmm


Ok. This is what my query grid looks like
Field: PositionID
Table: tblPersonalInfo
Sort:
Show: (Checked)
Criteria: Like "*" & [forms]![frmContact]![PositionID] & "*" And Like
"*"

I repeated this for statusid, clubid, and projectid...changing the
names as required. There are all on the first line of the
criteria...so they run like train.
for field email the criteria: is not null
and the or: is not null


If all is left blank pull all. If active is selected then pull all
active. If active and position is selected than pull all active and
all in youth position.
 
when i select a clubID it wont eliminate those records that dont have
that club. For instance i select rodeo club and the query gives me
back rodeo and sewing club...its like this with all of the fields. if
i select youth, it returns records with adults

????
 
Yes, that is correct. Because we are doing a "like with an asterisk. If
you want an exact match or blank, do...

[Forms]![frmContact]![PositionID] And Like "*"

Sorry, I missed that you wanted an exact match.

Change each one where you need an exact match. The previous way I gave you
allows a partial match and blank for all.
 
i fixed the error

but it is still not working.

when i select beef cluband run the query...it brings up all clubs
 
Step back and enter just one criteria. Get that one working. Then add
another. Step through the problem and figure out what is wrong. Create a
copy of your query and strip out all the criteria, then add them back one at
a time.

I have used the solution I gave you many times, so I know it will work.
Your issue is that you are doing this with many items. See if you can get
it to work with just one, then two, etc.
 
now when i try to run it it says error "you canceled the previous
operation"

this thing is driving me batty
 
it is now giving me an error saying "you have canceled the previous
operation" when i clikc on the button to bring the query
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If statement that returns all entries or one 2
Using NZ Function to return ALL records 4
#error - Blank 3
Search on entered or blank criteria 5
Query criteria - exclude data and return all records if null 3
NEED HELP with a multi-criteria query 2
query relelated to 2 combo boxes 2
Erratic results from query criteria 3

Back
Top