Using a Form to Run a Query/Print a Report

[J. Trucking]

Hello All,

I am trying to use a form to have user input to run a query. I have
got everything setup and running but still have a few questions that I
am hoping someone could help me with. I have looked at the forum
already and pulled together some information. The form would also
users to search on records for a piece of equipment that crushes
rock. I am hoping that when it's completed, the user could search on
all the records based on date (range of dates), customer, material
crushed, type of bar used to crush, and location of crushing.

1. I built the form with combo boxes to search through the table and
find all of the different entries that could be chosen (ie) the combo
box would show all of the different customers we've crushed rock for.
As for the dates, I used to text boxes and formatted them for mm/dd/
yy. The combo boxes work fine and the query recognizes these fine.
The problem lies with the dates. When I try to use the form to
control the dates (rather than control them manually) the query will
run but no records are produced. In the query, I did the following:

BETWEEN [Forms]![frmCrusherSearch]![StartDate] AND [Forms]!
[frmCrusherSearch]![EndDate]!

Like I said, the query will run, but no records show up. When I
replace the form reference with actual dates, it works fine.

2. Each record will display the engine hours and the start and end of
the day as well as the tonnes crushed at the start and end of the
day. What I would like to do is to be able to have an expression in
the query that will tell me the tonnes/hr crushed that day by
subtracting the End Values from the Start Values. I accomplished this
no problem in the query but one thing I forgot is that some days they
dont crush which means that start and end values are the
same...resulting in a #error. I would like a zero to display when
this #error appears. So, in place of the old expression, which was:

Expr1: ([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])

I put in...

Expr1: iff(iserr(([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart]),
0,([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])))

but the computer keeps telling me I have to few arguements.

3. I have this set up (form and query) and linked it to a report so
when the form opens, there is a button to "search" which will open the
report (and hopefully populate with all of the data). As i stated in
point #2, I would like to find out the tonnes/hr for each entry. I
would like to summarize all of the entries/records in the report by
finding the average tonnes/hr, total tonnes, and total hours at the
bottom of the report. Would I do this in the report itself?

My apologies for the long winded paragraph. I hope I have explained
myself well and I appreciate you taking the time to read this. Thanks
in advance for any help,

John

 

[George Nicholson]

1) try:

BETWEEN cdate([Forms]![frmCrusherSearch]![StartDate]) AND cdate([Forms]!
[frmCrusherSearch]![EndDate])

2) Remove a right parenthese from the very end of your iif() clause and
place it just before the first comma. IsErr() needs to be completed before
you get to the comma so it can return True or False for the 1st iif()
argument. At the moment it is incomplete (hence the 'too few arguments'
error). You have the correct # of parentheses, one is just misplaced.

3) Yes, I'd think the report itself is the way to go.

HTH

Hello All,

I am trying to use a form to have user input to run a query. I have
got everything setup and running but still have a few questions that I
am hoping someone could help me with. I have looked at the forum
already and pulled together some information. The form would also
users to search on records for a piece of equipment that crushes
rock. I am hoping that when it's completed, the user could search on
all the records based on date (range of dates), customer, material
crushed, type of bar used to crush, and location of crushing.

1. I built the form with combo boxes to search through the table and
find all of the different entries that could be chosen (ie) the combo
box would show all of the different customers we've crushed rock for.
As for the dates, I used to text boxes and formatted them for mm/dd/
yy. The combo boxes work fine and the query recognizes these fine.
The problem lies with the dates. When I try to use the form to
control the dates (rather than control them manually) the query will
run but no records are produced. In the query, I did the following:

BETWEEN [Forms]![frmCrusherSearch]![StartDate] AND [Forms]!
[frmCrusherSearch]![EndDate]!

Like I said, the query will run, but no records show up. When I
replace the form reference with actual dates, it works fine.

2. Each record will display the engine hours and the start and end of
the day as well as the tonnes crushed at the start and end of the
day. What I would like to do is to be able to have an expression in
the query that will tell me the tonnes/hr crushed that day by
subtracting the End Values from the Start Values. I accomplished this
no problem in the query but one thing I forgot is that some days they
dont crush which means that start and end values are the
same...resulting in a #error. I would like a zero to display when
this #error appears. So, in place of the old expression, which was:

Expr1: ([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])

I put in...

Expr1: iff(iserr(([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart]),
0,([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])))

but the computer keeps telling me I have to few arguements.

3. I have this set up (form and query) and linked it to a report so
when the form opens, there is a button to "search" which will open the
report (and hopefully populate with all of the data). As i stated in
point #2, I would like to find out the tonnes/hr for each entry. I
would like to summarize all of the entries/records in the report by
finding the average tonnes/hr, total tonnes, and total hours at the
bottom of the report. Would I do this in the report itself?

My apologies for the long winded paragraph. I hope I have explained
myself well and I appreciate you taking the time to read this. Thanks
in advance for any help,

John

 

[J. Trucking]

1) try:

BETWEEN cdate([Forms]![frmCrusherSearch]![StartDate]) AND cdate([Forms]!
[frmCrusherSearch]![EndDate])

2) Remove a right parenthese from the very end of your iif() clause and
place it just before the first comma. IsErr() needs to be completed before
you get to the comma so it can return True or False for the 1st iif()
argument. At the moment it is incomplete (hence the 'too few arguments'
error). You have the correct # of parentheses, one is just misplaced.

3) Yes, I'd think the report itself is the way to go.

HTH

Hello All,

I am trying to use a form to have user input to run a query. I have
got everything setup and running but still have a few questions that I
am hoping someone could help me with. I have looked at the forum
already and pulled together some information. The form would also
users to search on records for a piece of equipment that crushes
rock. I am hoping that when it's completed, the user could search on
all the records based on date (range of dates), customer, material
crushed, type of bar used to crush, and location of crushing.

1. I built the form with combo boxes to search through the table and
find all of the different entries that could be chosen (ie) the combo
box would show all of the different customers we've crushed rock for.
As for the dates, I used to text boxes and formatted them for mm/dd/
yy. The combo boxes work fine and the query recognizes these fine.
The problem lies with the dates. When I try to use the form to
control the dates (rather than control them manually) the query will
run but no records are produced. In the query, I did the following:

BETWEEN [Forms]![frmCrusherSearch]![StartDate] AND [Forms]!
[frmCrusherSearch]![EndDate]!

Like I said, the query will run, but no records show up. When I
replace the form reference with actual dates, it works fine.

2. Each record will display the engine hours and the start and end of
the day as well as the tonnes crushed at the start and end of the
day. What I would like to do is to be able to have an expression in
the query that will tell me the tonnes/hr crushed that day by
subtracting the End Values from the Start Values. I accomplished this
no problem in the query but one thing I forgot is that some days they
dont crush which means that start and end values are the
same...resulting in a #error. I would like a zero to display when
this #error appears. So, in place of the old expression, which was:

Expr1: ([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])

I put in...

Expr1: iff(iserr(([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart]),
0,([TonneEnd]-[TonneStart])/([EngineEnd]-[EngineStart])))

but the computer keeps telling me I have to few arguements.

3. I have this set up (form and query) and linked it to a report so
when the form opens, there is a button to "search" which will open the
report (and hopefully populate with all of the data). As i stated in
point #2, I would like to find out the tonnes/hr for each entry. I
would like to summarize all of the entries/records in the report by
finding the average tonnes/hr, total tonnes, and total hours at the
bottom of the report. Would I do this in the report itself?

My apologies for the long winded paragraph. I hope I have explained
myself well and I appreciate you taking the time to read this. Thanks
in advance for any help,

John- Hide quoted text -

- Show quoted text -

Thanks for taking the time to respond George

 

[J. Trucking]

Gary,

Thanks for the response. I had one problem with the iff statement I
put into my query. The computer is now telling me that I have an
"undefined function 'iff' in expression". Any ideas?

Thanks,

John

 

[Rick Brandt]

Gary,

Thanks for the response. I had one problem with the iff statement I
put into my query. The computer is now telling me that I have an
"undefined function 'iff' in expression". Any ideas?

It's not iff() It's iif()

Stands for Immediate-If

 

[J. Trucking]

I should learn to spell. Thanks for the help RIck