limiting a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my query I have a statement that asks for the entry of a car number, which displays only that car's information. The problem is that if a number is entered that has no records, a blank screen comes up. How can I put into the query that if there are no records for that number, a message appears stating that there are no records for that car... limiting the query

i.e. when the query is run, a message appears asking for a car number. If I enter "60", the records for car 60 appear. However, if I enter"64" for instance, nothing appears because there are obviously no records for a car by that number

I've set up a form to enter information on the cars by number. If I enter in "64" and not know that there is no "64", information could be entered on a car that doesn't exist

I hope this makes sense. My "access lingo" is limited. I'm proud of myself even to have gotten this far, but I hit a wall
Thanks for your help!!!
 
Hello,
1 way that u will give u the desired results is if u r
familiar with DAO or ADO coding u can open a recordset in
code check to see if the Recordcount property is greater
than zero...If it is then run the query u have already
created if not then display the desired message...Below I
put in an example of some DAO code

Function QueryRecordCount(x as string)
Dim db As DAO.Database
Dim rs DAO.Recordset


Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT Field1 FROM Table1 WHERE
Field1 = '" & X & "';", dbOpenSnapshot)

if rs.recordcount = 0 then
MsgBox "Desired Message..."
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Exit Function
end if
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function
-----Original Message-----
In my query I have a statement that asks for the entry of
Click to expand...
a car number, which displays only that car's information.
The problem is that if a number is entered that has no
records, a blank screen comes up. How can I put into the
query that if there are no records for that number, a
message appears stating that there are no records for that
car... limiting the query.
i.e. when the query is run, a message appears asking for
Click to expand...
a car number. If I enter "60", the records for car 60
appear. However, if I enter"64" for instance, nothing
appears because there are obviously no records for a car
by that number.
I've set up a form to enter information on the cars by
Click to expand...
number. If I enter in "64" and not know that there is
no "64", information could be entered on a car that
doesn't exist.
I hope this makes sense. My "access lingo" is limited.
Click to expand...
I'm proud of myself even to have gotten this far, but I
hit a wall.
 
In my query I have a statement that asks for the entry of a car number, which displays only that car's information. The problem is that if a number is entered that has no records, a blank screen comes up. How can I put into the query that if there are no records for that number, a message appears stating that there are no records for that car... limiting the query.

i.e. when the query is run, a message appears asking for a car number. If I enter "60", the records for car 60 appear. However, if I enter"64" for instance, nothing appears because there are obviously no records for a car by that number.

I've set up a form to enter information on the cars by number. If I enter in "64" and not know that there is no "64", information could be entered on a car that doesn't exist.

I hope this makes sense. My "access lingo" is limited. I'm proud of myself even to have gotten this far, but I hit a wall.
Thanks for your help!!!
Click to expand...

What is your "statement"? Are you opening a Query datasheet, or a Form
based on the query? The latter is a lot better - for one thing you can
trap the case where there are no records.

I'd also suggest using an unbound Form with a combo box so the user
doesn't need to guess at or memorize meaningless numbers. If they have
a Combo showing some identification for the car other than just its
number, then this problem will not arise since they can just select a
car from the list (and you know it's there); the criterion would be

=[Forms]![NameOfForm]![NameOfCombo]

instead of

=[Enter car number:]
 
Back
Top