Prompt for Date Issue

[gilescis]

I have a need to prompt a user for a date, When they enter that date the qry should do as follows.

if rec_date <= (DATE PROMPTED FOR) and rel_date is > (DATE PROMPTED FOR) ,
then
return Qty and a few other fields....

No the DATE PROMPTED FOR is not a field in the table.

Do I need it to be ?
If not what is the best way to tackle this

Thanks

 

[KenHigg]

You should be able to do something like:

if rec_date <= [Enter Start Date] and rel_date is > [Enter Ending Date]

???

 

[gilescis]

Is this placed in the QRY in the field section or criteria section ?

 

[KenHigg]

I'm think something more like the following in the criteria section:

Between [Enter Start Date] and [Enter Ending Date]

 

[gilescis]

Yes I can get that but what about the IF Ten Statement, where do I place that at..

 

[KenHigg]

Leave all that out

 

[gilescis]

But I only want it to return the data as follows
if rec_date <= Prompted [Enter Start Date] and rel_date is > Prompted [Enter Ending Date]
then
weight
else "disregard record"

If I leave the rest out it just returns all records in that date range I need it based on the qry

 

[KenHigg]

Ok... You have a table with some fields. One of them is a date field named rec_date. So you want all of the records where the rec_date is within a date range that the user enters when the query runs.

Right so far?

 

[gilescis]

No not really,
Yes I have a table with the following
rec_date
rel_date
weight
po#
I want to prompt the user for A date (not specificly the rec or rel date) just a date. then based on that date I wan the qry to see
if the rec_date <= the date they entered AND the rel_date is > the date they entered
then
Show me the values in the following field
rec_date rel_date weight po#

Thanks

 

[KenHigg]

Do you mind entering the date parameter twice?

 

[gilescis]

no because i can have it prompt like this

between[Enter Date]and[Confirm Date]

 

[KenHigg]

Then in the criteria for one date field put '<= [Enter Date]' and in the other date field, on the same line, put something like '>[Confirm Date]'. When they are on the same line in the criteria, it's the same as using AND which is similar to the 'between' method...

 

[Spam]

One way you can do this without prompting twice is to add another field to your query which prompts for the date, then use that value in your criteria for the other two fields.

eg. add a field with the following:

dateprompt: [Enter Date]

then for " rec_date "
Criteria is " <=[dateprompt] "

and for " rel_date "
Criteria is " >[dateprompt] "

 

[KenHigg]

One way you can do this without prompting twice is to add another field to your query which prompts for the date, then use that value in your criteria for the other two fields.

eg. add a field with the following:

dateprompt: [Enter Date]

then for " rec_date "
Criteria is " <=[dateprompt] "

and for " rel_date "
Criteria is " >[dateprompt] "

I be darn, I hadn't thought of that- Thanks

 

[gilescis]

Great work, I will try thanks for all
I knew there was a way and I knew what I wanted

Thanks Again

 

[gilescis]

well houston there is a prob,
This did not work
I need to attach my screenshots how is that done here, I uploaded the images but i can not find them

 

[GumbyD]

Try this SQL - you will need to replace all references of Rec_Table with your table/query name. You can paste this in the SQL view of a query.

PARAMETERS [Enter Date] DateTime;
SELECT Rec_Table.rec_Date, Rec_Table.rel_Date, Rec_Table.weight, Rec_Table.[PO#]
FROM Rec_Table
WHERE (((Rec_Table.rec_Date)<=[Enter Date]) AND ((Rec_Table.rel_Date)>=[Enter Date]));

 

[gilescis]

If I can get the attachment of my error to show up this is what it say"
The expression is typed incorrectly, or its to complex to be evaluated. Try simplifying the expression by assigning parts of the expression to variables"

No I removed just one of the criteria's and it 1/2 way wrks.

 

[gilescis]

Ok this is now what my SQL statement is, How ever if you look i have an expression that calculates [weight]*[percent full]
then returns the total weight.... But the way the statement is now it returns 0.00

PARAMETERS [Enter Date] DateTime;
SELECT RM_DATA.mat_type, RM_DATA.vendor, RM_DATA.po, RM_DATA.RC, RM_DATA.weight, RM_DATA.[percent full], [weight]*[percent full] AS Expr1, RM_DATA.rec_date, RM_DATA.reldate
FROM RM_DATA
WHERE (((RM_DATA.rec_Date)<=[Enter Date]) AND ((RM_DATA.rel_Date)>=[Enter Date]));

 

[GumbyD]

What datatypes are the two fields you are multipling? If either of them is non-numeric, you will either need to change the datatype or use a conversion function to convert the data to a numeric datatype (cint converts to an integer, cdbl converts to a double). You could try this expression:
cdbl([weight])*Cdbl([percent full]) AS TotalWeight