Funny you mention the time thing, I noticed in my table
"NPLC_Report_Parameters" even though I have Short Date selected I didn't have
the input mask to 99/99/99;0, which helped me in the field RP_Today but not
in RP_Date and this is what I think:
I have an unbound drop down box that has the following options to select:
Today, Yesterday, 1 week ago, 2 weeks ago, etc and for that combo box for
After Update based on what they pick is
If [cmb_Time_Frame] = "Current Day" Then
[RP_Date] = Now()
ElseIf [cmb_Time_Frame] = "Yesterday" Then
[RP_Date] = Now() - 1
and even though the the field RP_Date if formated to have the input mask of
99/99/99;0 it still shows up the time.
Now is there a way in my VB code to format RP_Date?
I so appreciate your time and help and believe we are nearing the end.
Thanks!
JL said:
Hi Stacey,
I try to create a similar database based on what you have told me. There is
no problem for me to get the right date back. I can not create the problem
that you are described.
However, looking your query closely. I have notice there is a "Like
[RP_Mold]" and "= [RP_Plant]". Those 2 have also to be matched in order to
be selected. Like statement without a wild card (*) is like "=". So, I
guess that might be your problem.
If not, there is another possiblility. If you store time in Occurrence_Date
field. That might be the cause as well. Since "1/3/2005" and "1/3/2005
06:00:00 AM" is not considered in "1/3/2005".
Hope this will help.
:
I have a form that you choose criteria that dumps into the table
"NPLC_Report_Parameters", then I have the main table where all input data is
stored in "tbl_NPLC_Main" from here I created the below query having both
tables in the query and using the information stored in the table
"NPLC_Report_Parameters" as the query parameters. They are not joined.
Both fields [RP_Date] And [RP_Today] are in the "NPLC_Report_Parameters" and
used as the query criteria for [Occurrence_Date] in "tbl_NPLC_Main" and yes
they are all date fields.
The reason I did it this way is there are many charts and reports that are
based on the same information that if they enter it once is better than
having to enter
Now if I tell the form to back [RP_Date] back 1 additional day then I get
the 1/3/05 I am missing but I don't think that is an accurate way to do it.
If there is any way I can send you a print screen I will.
Thanks again!
:
Hi Stacey,
Questions?
1) What is "NPLC_Report_Parameters"? Are you join this table? If yes, I do
not see the JOIN statement.
2) What are [RP_Date] And [RP_Today]? Are they fields from the table? or
are you enter a value when it ask you for it? Are they Date type fields?
:
SELECT tbl_NPLC_Main.Occurrence_Date, tbl_NPLC_Main.Mold_Number,
tbl_NPLC_Main.Plant, tbl_NPLC_Main.Total_Inspected
FROM tbl_NPLC_Main, NPLC_Report_Parameters
WHERE (((tbl_NPLC_Main.Occurrence_Date) Between [RP_Date] And [RP_Today])
AND ((tbl_NPLC_Main.Mold_Number) Like [RP_Mold]) AND
((tbl_NPLC_Main.Plant)=[RP_Plant]))
ORDER BY tbl_NPLC_Main.Occurrence_Date;
I compared this statement to when I can enter my parameters [Start] and
[End] and I didn't see any difference.
Thanks again.
:
Hi Stacey,
I do not know what "#" is stand for. But, it work with date string value.
I hope that you do not have "?" at the end.
Can you show me the query?
:
No, what do the #'s stand for?
I tried that and I get "Data Type Mismatch in criteria expression"
Any other suggestions?
Thanks!
:
Hi Stacey,
Did you try
Between "#" & [RP_Date] & "#" and "#" & [RP_Today] & "#"?
:
I have a query based on parameters out of a table so it reads:
Between [RP_Date] and [RP_Today]
(which should be between 1/3/05 and 1/10/05) but it returns values of 1/4/05
through 1/7/05(this last one is ok because there is no 1/8 - 1/10 data).
But if I change the parameters to:
Between [Start Date] and [End Date] and enter 1/3/05 and 1/10/05 I get
results of 1/3/05 through 1/7/05.
How can I get 2 different results of the same basic idea?
Thanks!!
Stacey
