Date Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

1. I need to know why this works with one ID, but not the other.
Under Date field I have
Between [##] And DateAdd("d",-6,Date()))
When I type the criteria for a specific ID my results are right.
When I use a different ID I get all records from the date I
entered to the end of existing records. I've looked at every
thing I can, and have no clue what's wrong.
2.I'm trying to have user only type in one date, and have the
second date be 6 days less than the date they type in.
Is code correct for that, if not, what should it be?
 
What are you entereing for ##? To use 'between' you would need to enter a
date. The purpose of 'between' is to check for all entries between two
dates.

Not sure I understand where the "ID" comes into the picture.

What is your query? Post the sql so we can see it.

Thanks.
 
My query has fields: ID, Date, and 3 more fields.
I have [Enter ID] undder IDfield to retrieve just those records.
I do type a date under the between expression.
Here is my SQL:

SELECT tblMinutes.[Med RecNumber], tblMinutes.Date, tblMinutes.PT,
tblMinutes.OT, tblMinutes.ST
FROM tblMinutes
WHERE (((tblMinutes.[Med RecNumber])=[Enter #]) AND ((tblMinutes.Date)
Between [##] And DateAdd("d",-7,Date())));


Rick B said:
What are you entereing for ##? To use 'between' you would need to enter a
date. The purpose of 'between' is to check for all entries between two
dates.

Not sure I understand where the "ID" comes into the picture.

What is your query? Post the sql so we can see it.

Thanks.


dar said:
1. I need to know why this works with one ID, but not the other.
Under Date field I have
Between [##] And DateAdd("d",-6,Date()))
When I type the criteria for a specific ID my results are right.
When I use a different ID I get all records from the date I
entered to the end of existing records. I've looked at every
thing I can, and have no clue what's wrong.
2.I'm trying to have user only type in one date, and have the
second date be 6 days less than the date they type in.
Is code correct for that, if not, what should it be?
Click to expand...
Click to expand...
 
Try:

SELECT tblMinutes.[Med RecNumber], tblMinutes.[Date], tblMinutes.PT,
tblMinutes.OT, tblMinutes.ST
FROM tblMinutes
WHERE (((tblMinutes.[Med RecNumber])=[Enter RecordID:])
AND ((tblMinutes.[Date])
Between DateAdd("d",-7,[Enter StartDate:])
And [Enter StartDate:]));

BTW, "Date" is not a good choice for a Field name as there is a Date()
function and a Date statement. I ensure that there is no mix-up by using
square brackets but it is much better if you can change the Field name.

HTH
Van T. Dinh
MVP (Access)
 
Thanks, I'll try suggestion. My question is, do they have to type in the date
twice. I only want them to type it once and have it automatically fill in
where
it needs to be.
Van T. Dinh said:
Try:

SELECT tblMinutes.[Med RecNumber], tblMinutes.[Date], tblMinutes.PT,
tblMinutes.OT, tblMinutes.ST
FROM tblMinutes
WHERE (((tblMinutes.[Med RecNumber])=[Enter RecordID:])
AND ((tblMinutes.[Date])
Between DateAdd("d",-7,[Enter StartDate:])
And [Enter StartDate:]));

BTW, "Date" is not a good choice for a Field name as there is a Date()
function and a Date statement. I ensure that there is no mix-up by using
square brackets but it is much better if you can change the Field name.

HTH
Van T. Dinh
MVP (Access)



dar said:
My query has fields: ID, Date, and 3 more fields.
I have [Enter ID] undder IDfield to retrieve just those records.
I do type a date under the between expression.
Here is my SQL:

SELECT tblMinutes.[Med RecNumber], tblMinutes.Date, tblMinutes.PT,
tblMinutes.OT, tblMinutes.ST
FROM tblMinutes
WHERE (((tblMinutes.[Med RecNumber])=[Enter #]) AND ((tblMinutes.Date)
Between [##] And DateAdd("d",-7,Date())));
Click to expand...
Click to expand...
 
No, the user only have to type the date once. Access will pick up that the
second one is the same with the first one.

Make you you have *exactly* the same spelling for the 2 instances of the
date parameter.
 
Perfect. I really thank you for your help.
Now, is there some way to get the results of the "SumofPT" from that query
into a text box on my main form.
My main form has
Med Rec Number, LName, FName
My subform has all of the records (many) of the query fields.
The command button on main form generates the query for SumofPT
 
If this is simply the sum of PT values in the Subform and you want to
display it on the main Form, there is an example in the [Orders Form] /
[Orders Subform] combination of the sample database NorthWind. The Trick is
to use a (hidden) TextBox in the footer of the Form being used as the
SourceObject of the SubformControl.

Check out the sample database "NorthWind" noting carefully the TextBox
Control in the Form footer of the [Orders Subform].
 
I'll check that out, however, it is not just a sum from subform. It is a
certain
selection of records of the subform, based on a query generated by a command
button. I do have another form that gives me the correct results, but I would
like those results to show up on the main form.
Van T. Dinh said:
If this is simply the sum of PT values in the Subform and you want to
display it on the main Form, there is an example in the [Orders Form] /
[Orders Subform] combination of the sample database NorthWind. The Trick is
to use a (hidden) TextBox in the footer of the Form being used as the
SourceObject of the SubformControl.

Check out the sample database "NorthWind" noting carefully the TextBox
Control in the Form footer of the [Orders Subform].

--
HTH
Van T. Dinh
MVP (Access)


dar said:
Perfect. I really thank you for your help.
Now, is there some way to get the results of the "SumofPT" from that query
into a text box on my main form.
My main form has
Med Rec Number, LName, FName
My subform has all of the records (many) of the query fields.
The command button on main form generates the query for SumofPT
Click to expand...
Click to expand...
 
Back
Top