Use of Variables in queries

[hkhella]

I have multiple client names listed in a field. I need to build a report for
seperate clients with similar search criteria (i.e. search for reports that
have not been faxed for cleint A, then search for reports for client B that
have not been faxed). The search parameters are the same for all clients
and the variable will be the client. I would like to have a drop down list
of clients to choose to run each search. Can someone assist me with building
this setup. I am a novice Access user and detailed assistance would be
greatly appreciated.

Thank you

 

[Jeff Boyce]

Access queries don't have "drop-down" lists.

Another approach is to build a form that DOES have the drop-down list you
want, then modify your query to use the value in the form's drop-down as a
parameter.

In the Criteria row, it would look something like:
Forms!YourForm!YourDropDown

Check parameter query in Access HELP.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Klatuu]

If you are creating a report and want that report filtered by a specific
client, I would suggest you not do the filtering in the query, but use the
Where argument of the Open Report method. You say you have multiple client
names listed in a field, but that doesn't say much. You could mean "I have a
table with a Client field and I have multiple clients" or, you could mean "I
have a (combo box/list box) control on my form that lists my clients". It is
hard to tell because a lot of people that use the word field when they really
mean control. Not your fault, Microsoft is the most guilty of this. Fields
are properties of tables and queries. Forms have controls that are either
unbound or bound to fields.

Most likely, you have a combo box that lists your clients. If this is
correct, you can filter the report like this:

DoCmd.OpenReport "MyReportName", , , "[CliendID] = '" & Me.cboClient & "'"

Assuming ClientID is a field in the report's record source, the report will
be filtered on the content of the combo box named cboClient.

 

[hkhella]

THe variable is from a related field called Account.ClientMainAccount The
client is the Table and the field is the ClientMainAccount. Could you guide
me with this setup?

Thank you

If you are creating a report and want that report filtered by a specific
client, I would suggest you not do the filtering in the query, but use the
Where argument of the Open Report method. You say you have multiple client
names listed in a field, but that doesn't say much. You could mean "I have a
table with a Client field and I have multiple clients" or, you could mean "I
have a (combo box/list box) control on my form that lists my clients". It is
hard to tell because a lot of people that use the word field when they really
mean control. Not your fault, Microsoft is the most guilty of this. Fields
are properties of tables and queries. Forms have controls that are either
unbound or bound to fields.

Most likely, you have a combo box that lists your clients. If this is
correct, you can filter the report like this:

DoCmd.OpenReport "MyReportName", , , "[CliendID] = '" & Me.cboClient & "'"

Assuming ClientID is a field in the report's record source, the report will
be filtered on the content of the combo box named cboClient.
--
Dave Hargis, Microsoft Access MVP

I have multiple client names listed in a field. I need to build a report for
seperate clients with similar search criteria (i.e. search for reports that
have not been faxed for cleint A, then search for reports for client B that
have not been faxed). The search parameters are the same for all clients
and the variable will be the client. I would like to have a drop down list
of clients to choose to run each search. Can someone assist me with building
this setup. I am a novice Access user and detailed assistance would be
greatly appreciated.

Thank you

 

[Klatuu]

I would be happy to, but I need a little more info. We have to be specific
between what is a field and what is a control. Tables have fields. Forms
have controls. There really are no fields on a form. I know some Microsoft
documentation calls them fields, but they are not, really. It does help to
use that distinction so we know for sure what we are talking about. So, here
is what I need to know:

What is the name of the control on the form where the Client is carried?

If the control where the client is carried is a list box, is it a multi
select list box?

If is is a multi select list box, will you want to filter the report on the
selected items in the List Box? That is, more than one client.

What is the name of the field in the form's record source where the Client
is carried?

Is the Client field in the record source a text field or a numeric field?

If you would be kind enough to provide this detail, I will be happy to show
you how to make this work.
--
Dave Hargis, Microsoft Access MVP

THe variable is from a related field called Account.ClientMainAccount The
client is the Table and the field is the ClientMainAccount. Could you guide
me with this setup?

Thank you

If you are creating a report and want that report filtered by a specific
client, I would suggest you not do the filtering in the query, but use the
Where argument of the Open Report method. You say you have multiple client
names listed in a field, but that doesn't say much. You could mean "I have a
table with a Client field and I have multiple clients" or, you could mean "I
have a (combo box/list box) control on my form that lists my clients". It is
hard to tell because a lot of people that use the word field when they really
mean control. Not your fault, Microsoft is the most guilty of this. Fields
are properties of tables and queries. Forms have controls that are either
unbound or bound to fields.

Most likely, you have a combo box that lists your clients. If this is
correct, you can filter the report like this:

DoCmd.OpenReport "MyReportName", , , "[CliendID] = '" & Me.cboClient & "'"

Assuming ClientID is a field in the report's record source, the report will
be filtered on the content of the combo box named cboClient.
--
Dave Hargis, Microsoft Access MVP

I have multiple client names listed in a field. I need to build a report for
seperate clients with similar search criteria (i.e. search for reports that
have not been faxed for cleint A, then search for reports for client B that
have not been faxed). The search parameters are the same for all clients
and the variable will be the client. I would like to have a drop down list
of clients to choose to run each search. Can someone assist me with building
this setup. I am a novice Access user and detailed assistance would be
greatly appreciated.

Thank you