473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Parameter Query

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, 120 views)
Oct 5 '18 #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

8 1237
PhilOfWalton
1,430 Expert 1GB
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
Oct 5 '18 #2
Rabbit
12,516 Expert Mod 8TB
Please use code tags when posting code or formatted data.

The reason it's asking for it twice is because you spelled them differently.
Oct 5 '18 #3
PhilOfWalton
1,430 Expert 1GB
@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, 252 views)
Oct 5 '18 #4
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
Oct 5 '18 #5
PhilOfWalton
1,430 Expert 1GB
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, 131 views)
Oct 5 '18 #6
The work orders are downloaded from a database corporate has no data will be entered.
Oct 5 '18 #7
ile was uploaded successfuly on TinyUpload.com server.
You can download it from address: http://s000.tinyupload.com/?file_id=...98652376872171
Oct 5 '18 #8
PhilOfWalton
1,430 Expert 1GB
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
Oct 5 '18 #9

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

Similar topics

5
by: MX1 | last post by:
Simpler way to ask question from my previous post. I wrote a query and it has a paramter field in it. I want to enter a date with the current year. If it I put in 6/30/2003, it works great. If I...
1
by: Cory | last post by:
When I run the subroutine testEmailContacts the msgbox says that there is only 1 record. The sql for qyEmailContactsQyCard is below. There is over 3000 records in the table "tbl:Contact". What am i...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
1
by: carrionk | last post by:
Hi, I have created a Subform which SourceObject is a parameter query. This is the Query: Qry Name:80IsscomProduct SELECT * FROM Isscomp28 WHERE Like ;
1
by: john_stivenson | last post by:
I have a subform whose source is a parameter query. How can I programatically change query parameters and refresh subform without showing "Enter Parameter Value" dialog?
0
by: AP | last post by:
I use the following snippet to open a build a querydef. This works great with a query that has a parameter that points to a form, but if I try to use it in a parameter query, even if the parameter...
1
by: jmarr02s | last post by:
When I double click on my form..I get a Parameter Query box. How can I turn this off?! I went to properties, but did not see anything that would help remove this. I also deleted the query...
3
by: limperger | last post by:
Hello everyone: This question is neither urgent nor important, but I post it just in case anyone knows (and wants to give a reply, of course XD). I have little idea of VBA, but I have dared to...
4
by: chicane | last post by:
I am currently writing a query in Access. I need to set up a parameter query for three fields which are called Status (text field), Authorised (date field) and Legal notices (date field). I need...
2
by: brat33 | last post by:
I am trying to modify some code to create a mail merged label document within Word 2007, using a Access 2007 Parameter Field. My issue comes about when I cannot see the Parameter Query within the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.