423,484 Members | 2,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,484 IT Pros & Developers. It's quick & easy.

Parameter Query

P: 14
I have a query that has a Between Enter the start date and end date but when i run the query it ask me to enter the dates twice.

Here the SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(MaximoReportT.WorkOrderID) AS CountOfWorkOrderID
  2. FROM MaximoReportT
  3. WHERE (((MaximoReportT.WorkType) In ("PMINS","PMOR","PMPDM","PMREG","PMRT")) AND ((MaximoReportT.TargetStartDate) Between [Enter Start Date] And [Enter End Date]) AND ((MaximoReportT.Status)="COMP") AND ((MaximoReportT.ActualLaborHours)<>"00:00") AND ((MaximoReportT.ActualStartDate) Between [Enter the Start Date] And [Enter the End Date]));
Attached Images
File Type: jpg query.jpg (93.5 KB, 8 views)
1 Week Ago #1

✓ answered by PhilOfWalton

Your Query asks for the dates to enter dates for both the TargetStartDate and the ActualStatrDate. Access assumes that you may well enter different date ranges for these 2 values.

In a different post, I sent you a small Db with a date entry form. If you set the dates up in that, and your criteria to

Expand|Select|Wrap|Line Numbers
  1. Between Forms!FrmDates!TxtStartDate and Forms!FrmDates!TxtEndDate
that should work, as well as any other queries based on dates.

Phil

Share this Question
Share on Google+
8 Replies


PhilOfWalton
Expert 100+
P: 1,353
Your Query asks for the dates to enter dates for both the TargetStartDate and the ActualStatrDate. Access assumes that you may well enter different date ranges for these 2 values.

In a different post, I sent you a small Db with a date entry form. If you set the dates up in that, and your criteria to

Expand|Select|Wrap|Line Numbers
  1. Between Forms!FrmDates!TxtStartDate and Forms!FrmDates!TxtEndDate
that should work, as well as any other queries based on dates.

Phil
1 Week Ago #2

Rabbit
Expert Mod 10K+
P: 12,265
Please use code tags when posting code or formatted data.

The reason it's asking for it twice is because you spelled them differently.
1 Week Ago #3

PhilOfWalton
Expert 100+
P: 1,353
@Jennie

Actually I think you will regret having things like
Expand|Select|Wrap|Line Numbers
  1. ("PMINS","PMOR","PMPDM","PMREG","PMRT")
and
Expand|Select|Wrap|Line Numbers
  1. MaximoReportT.Status)="COMP")
in your queries.

Hard coding values into your queries may work for the moment, but there will be all hell to pay if you need to add another parameter, or change an existing parameter. All your queries and any forms or reports based on those queries will probably need redesigning ... till the next change.

You can avoid the problem by setting up something like this illustration to give complete flexibility.



So with any particular calculation, you can specify which parameers to include, and which to exclude. Changing those parameters is no problem.

Phil
Attached Images
File Type: jpg Calculation.jpg (138.2 KB, 12 views)
1 Week Ago #4

P: 14
Phill i Like that idea
i ordered my headphones and i hoping to set some time next week to sit down with you and have you help me
1 Week Ago #5

PhilOfWalton
Expert 100+
P: 1,353
I look forward to that, but be warned, you will have a lot of work to do. I have done quite a lot with your Db, but it has taken a fair number of hours, so don't expect to get what you want that quickly

This partially completed form may wet your appetite.


Controls ending in ID will become Combo Boxes with meaningful information.

You will note that in the area highlighted in yellow, that the description occurs 3 times but with slight differences in the position of the comma. There were already about 1600 descriptions which occurred more than once, and in the example above (and there are many similar) 2 of those descriptions are redundant.

That is why we need to normalise your database, then adding new works orders becomes largely a matter of picking existing information from a Combo Box or List Box... much less typing.

Phil
Attached Images
File Type: jpg FrmWorksOrder.jpg (300.7 KB, 12 views)
1 Week Ago #6

P: 14
The work orders are downloaded from a database corporate has no data will be entered.
1 Week Ago #7

P: 14
ile was uploaded successfuly on TinyUpload.com server.
You can download it from address: http://s000.tinyupload.com/?file_id=...98652376872171
1 Week Ago #8

PhilOfWalton
Expert 100+
P: 1,353
Well I am somewhat annoyed.

I refer to your post #5 in question https://bytes.com/topic/desktop-soft...criteria-query

You didn't indicate the data was continually coming from your Excel file which is continually being updated and changed (Question 3)

As there are no rules for what is entered into Excel, as far as I can see, it will be impossible to normalise the database.

I think, therefor, that others may be able to help you better than I can

Phil
1 Week Ago #9

Post your reply

Sign in to post your reply or Sign up for a free account.