HELP with queries!!!

[confused with access]

Hi all,

I am a new user to Access and need some help with setting up a query. I am
building the framework to an inventory database. Currently I have one table
that has the following information:

serial #
Status (checked in or checked out)
equipment type (pc, network printer, standalone printer, scanner, and monitor)
EE# (equipment number)
model
manufacturer
work order#
department (where the equipment is going)
equipment location (the room number)
relocation date
comments

I understand how to run the query, but I am trying to include drop down
menu's in the query instead of having to type it in. For example, the query I
am trying to run I'm checking to see how many monitors are checked in.
Instead of typing in "checked in" and "monitors" my boss would like to have a
drop down menu to choose from.

#2 When I try to perform a wild card search, what do I need to do so that it
will show everything that is in the entire inventory, even if I do not enter
any information into the parameter boxes?

#3- I am trying to set an automatic date so that when one of our PC guys
fills in a form, today's date will automatically pop up in that box without
them having to type anything, BUT if they would like to change the date they
can.

Any help or advice that you can give will be helpful. Thanks!!

 

[Philip Herlihy]

Hi all,

I am a new user to Access and need some help with setting up a query. I am
building the framework to an inventory database. Currently I have one table
that has the following information:

serial #
Status (checked in or checked out)
equipment type (pc, network printer, standalone printer, scanner, and monitor)
EE# (equipment number)
model
manufacturer
work order#
department (where the equipment is going)
equipment location (the room number)
relocation date
comments

I understand how to run the query, but I am trying to include drop down
menu's in the query instead of having to type it in. For example, the query I
am trying to run I'm checking to see how many monitors are checked in.
Instead of typing in "checked in" and "monitors" my boss would like to have a
drop down menu to choose from.

#2 When I try to perform a wild card search, what do I need to do so that it
will show everything that is in the entire inventory, even if I do not enter
any information into the parameter boxes?

#3- I am trying to set an automatic date so that when one of our PC guys
fills in a form, today's date will automatically pop up in that box without
them having to type anything, BUT if they would like to change the date they
can.

Any help or advice that you can give will be helpful. Thanks!!

Nice clear question. I'm going to respond in reverse order (simplest
first!).

Q3: All you need to do is add a default value to the date/time field in
your table, and set that to =date(). The result of the "date" function
will be entered into that field whenever a new record is created, but it
can subsequently be edited. The now() function returns both the date
and the time: dates/times are represented as integer/fractional parts of
a single number.

Q2: You're using a "parameter" query, which triggers Access to prompt
you for the missing value.

All you need to do is put an asterisk (*) in the field and it'll match
everything. Your Criterion needs to use the Like operator, rather than
'='. Then you can also search for things like "*abc*xy?" where the '?'
matches a single character rather than any number.

Q1: You can't put drop-downs in a query - you need a form. It's true
that you can have "Lookup" fields in a table, but many people regard
this as bad practice, and I avoid them as it obscures the table design.

There's an art to searching based on a form. You might like to look at
this page for an extended account: http://allenbrowne.com/ser-62.html

You can also develop a basic search form using the "Query by Form"
technique: http://support.microsoft.com/kb/304428
It can be tricky getting the hang of referring to a "box" where a user
will enter a match-string. This (scary) page will help:
http://www.mvps.org/access/forms/frm0031.htm
... but if you can get the hang of the Expression Builder that will make
this much easier. See:
http://office.microsoft.com/en-us/access/HP051866381033.aspx
http://office.microsoft.com/en-us/access/HA102549021033.aspx (demo)

However, it's worth pointing out that Access comes with "searching"
facilities out-of-the box - "Filter by Form" - which might be all you
need. See: http://support.microsoft.com/kb/304259
Filtering is very similar to using a query with a criterion, but Access
hides the query from you. You'll find that described in Help.

Hope that helps. We're all learning!

Phil, London

 

[Duane Hookom]

Your users deserve a form with controls for all interaction. You can set
defaults, use combo or list boxes, check for integrity, etc. Your query might
have a criteria under [EquipmentType] like:

Forms!frmSearch!cboEquipType or Forms!frmSearch!cboEquipType is Null