Dates

  • Thread starter Thread starter Lambi000
  • Start date Start date
L

Lambi000

I'm using a form for a from/to date for users. I was using the calendar
control but I ended up with some bizarre "froms" and "tos". I set the "from"
to 1/20/2008 and got all the records from that date from 7:52 AM on....but I
was missing seven records from 12:27 AM to the aforementioned date. The "to"
gave me half the records for the date 01//26/2008 but didn't give me any
records past 3:29 PM.

I switched from the calender tool and put in a text box and now the user
types in the date, but in order for me to get 0/26/2008 records, I have to
input 01/27/2008. That gives me all of 1/26 records and none of 1/27.

BTW, I'm using the short date format.

Thanx!

I think I had this problem before but something is getting real weird here.
Any clues as to what's happening?
 
I believe the "short date form" applies to the value displayed, not the
value stored.

Are you storing the Date-only value (using something like the Date()
function), or a Date/Time value (using something like the Now() function)?

When you say "get", how are you getting? Are you using a query? If so,
please post the SQL statement.

We aren't there, we can't see how you are inputting "01/27/2008", nor where.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sorry, here's the SQL. I'm using "Between" Text Boxes that the user inputs.

SELECT dbo_fShopActivity.ActualFinishTime, dbo_fShopActivity.TransactionNum,
dbo_fShopActivity.Employee_CrewID, dbo_fShopActivity.ShiftSetAssigned,
dbo_fShopActivity.ShiftAssigned, dbo_fShopActivity.DepartmentNum,
dbo_fShopActivity.WorkCenterNum, dbo_fShopActivity.rWindow_I_C_S,
dbo_fShopActivity.TransactionName, dbo_fShopActivity.RecordStatus,
dbo_fShopActivity.ActualStartTime, dbo_fShopActivity.AdjustedStartTime,
dbo_fShopActivity.ActualFinishTime, dbo_fShopActivity.AdjustedFinishTime,

Etc. etc. a whole bunch of other fields

dbo_fShopActivity.SerialNum, dbo_fShopActivity.LotNum,
MIRR_SFBP602UF_IIMEXT.INNLBS, MIRR_SFBP602UF_IIMEXT.UNQTY
FROM dbo_fShopActivity LEFT JOIN MIRR_SFBP602UF_IIMEXT ON
dbo_fShopActivity.ItemNum = MIRR_SFBP602UF_IIMEXT.IPROD
WHERE (((dbo_fShopActivity.ActualFinishTime) Between ([Forms]![Report
Parameters]![Text67]) And ([Forms]![Report Parameters]![Text69])))
ORDER BY dbo_fShopActivity.ActualFinishTime;
 
Can you rewrite the query and use "hard-coded" date values and have it work
correctly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lambi000 said:
Sorry, here's the SQL. I'm using "Between" Text Boxes that the user
inputs.

SELECT dbo_fShopActivity.ActualFinishTime,
dbo_fShopActivity.TransactionNum,
dbo_fShopActivity.Employee_CrewID, dbo_fShopActivity.ShiftSetAssigned,
dbo_fShopActivity.ShiftAssigned, dbo_fShopActivity.DepartmentNum,
dbo_fShopActivity.WorkCenterNum, dbo_fShopActivity.rWindow_I_C_S,
dbo_fShopActivity.TransactionName, dbo_fShopActivity.RecordStatus,
dbo_fShopActivity.ActualStartTime, dbo_fShopActivity.AdjustedStartTime,
dbo_fShopActivity.ActualFinishTime, dbo_fShopActivity.AdjustedFinishTime,

Etc. etc. a whole bunch of other fields

dbo_fShopActivity.SerialNum, dbo_fShopActivity.LotNum,
MIRR_SFBP602UF_IIMEXT.INNLBS, MIRR_SFBP602UF_IIMEXT.UNQTY
FROM dbo_fShopActivity LEFT JOIN MIRR_SFBP602UF_IIMEXT ON
dbo_fShopActivity.ItemNum = MIRR_SFBP602UF_IIMEXT.IPROD
WHERE (((dbo_fShopActivity.ActualFinishTime) Between ([Forms]![Report
Parameters]![Text67]) And ([Forms]![Report Parameters]![Text69])))
ORDER BY dbo_fShopActivity.ActualFinishTime;


Jeff Boyce said:
I believe the "short date form" applies to the value displayed, not the
value stored.

Are you storing the Date-only value (using something like the Date()
function), or a Date/Time value (using something like the Now()
function)?

When you say "get", how are you getting? Are you using a query? If so,
please post the SQL statement.

We aren't there, we can't see how you are inputting "01/27/2008", nor
where.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Jeff,

What do you mean by "hard-coded"?

Jeff Boyce said:
Can you rewrite the query and use "hard-coded" date values and have it work
correctly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lambi000 said:
Sorry, here's the SQL. I'm using "Between" Text Boxes that the user
inputs.

SELECT dbo_fShopActivity.ActualFinishTime,
dbo_fShopActivity.TransactionNum,
dbo_fShopActivity.Employee_CrewID, dbo_fShopActivity.ShiftSetAssigned,
dbo_fShopActivity.ShiftAssigned, dbo_fShopActivity.DepartmentNum,
dbo_fShopActivity.WorkCenterNum, dbo_fShopActivity.rWindow_I_C_S,
dbo_fShopActivity.TransactionName, dbo_fShopActivity.RecordStatus,
dbo_fShopActivity.ActualStartTime, dbo_fShopActivity.AdjustedStartTime,
dbo_fShopActivity.ActualFinishTime, dbo_fShopActivity.AdjustedFinishTime,

Etc. etc. a whole bunch of other fields

dbo_fShopActivity.SerialNum, dbo_fShopActivity.LotNum,
MIRR_SFBP602UF_IIMEXT.INNLBS, MIRR_SFBP602UF_IIMEXT.UNQTY
FROM dbo_fShopActivity LEFT JOIN MIRR_SFBP602UF_IIMEXT ON
dbo_fShopActivity.ItemNum = MIRR_SFBP602UF_IIMEXT.IPROD
WHERE (((dbo_fShopActivity.ActualFinishTime) Between ([Forms]![Report
Parameters]![Text67]) And ([Forms]![Report Parameters]![Text69])))
ORDER BY dbo_fShopActivity.ActualFinishTime;


Jeff Boyce said:
I believe the "short date form" applies to the value displayed, not the
value stored.

Are you storing the Date-only value (using something like the Date()
function), or a Date/Time value (using something like the Now()
function)?

When you say "get", how are you getting? Are you using a query? If so,
please post the SQL statement.

We aren't there, we can't see how you are inputting "01/27/2008", nor
where.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using a form for a from/to date for users. I was using the
calendar
control but I ended up with some bizarre "froms" and "tos". I set the
"from"
to 1/20/2008 and got all the records from that date from 7:52 AM
on....but
I
was missing seven records from 12:27 AM to the aforementioned date.
The
"to"
gave me half the records for the date 01//26/2008 but didn't give me
any
records past 3:29 PM.

I switched from the calender tool and put in a text box and now the
user
types in the date, but in order for me to get 0/26/2008 records, I have
to
input 01/27/2008. That gives me all of 1/26 records and none of 1/27.

BTW, I'm using the short date format.

Thanx!

I think I had this problem before but something is getting real weird
here.
Any clues as to what's happening?
Click to expand...
Click to expand...
Click to expand...
 
Instead of using a parameter for the user to input a date, use something
like #1/2/2003# (i.e., "hard-code" the dates ... put actual date values into
the query instead of using the parameters).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lambi000 said:
Jeff,

What do you mean by "hard-coded"?

Jeff Boyce said:
Can you rewrite the query and use "hard-coded" date values and have it
work
correctly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Lambi000 said:
Sorry, here's the SQL. I'm using "Between" Text Boxes that the user
inputs.

SELECT dbo_fShopActivity.ActualFinishTime,
dbo_fShopActivity.TransactionNum,
dbo_fShopActivity.Employee_CrewID, dbo_fShopActivity.ShiftSetAssigned,
dbo_fShopActivity.ShiftAssigned, dbo_fShopActivity.DepartmentNum,
dbo_fShopActivity.WorkCenterNum, dbo_fShopActivity.rWindow_I_C_S,
dbo_fShopActivity.TransactionName, dbo_fShopActivity.RecordStatus,
dbo_fShopActivity.ActualStartTime, dbo_fShopActivity.AdjustedStartTime,
dbo_fShopActivity.ActualFinishTime,
dbo_fShopActivity.AdjustedFinishTime,

Etc. etc. a whole bunch of other fields

dbo_fShopActivity.SerialNum, dbo_fShopActivity.LotNum,
MIRR_SFBP602UF_IIMEXT.INNLBS, MIRR_SFBP602UF_IIMEXT.UNQTY
FROM dbo_fShopActivity LEFT JOIN MIRR_SFBP602UF_IIMEXT ON
dbo_fShopActivity.ItemNum = MIRR_SFBP602UF_IIMEXT.IPROD
WHERE (((dbo_fShopActivity.ActualFinishTime) Between ([Forms]![Report
Parameters]![Text67]) And ([Forms]![Report Parameters]![Text69])))
ORDER BY dbo_fShopActivity.ActualFinishTime;


:

I believe the "short date form" applies to the value displayed, not
the
value stored.

Are you storing the Date-only value (using something like the Date()
function), or a Date/Time value (using something like the Now()
function)?

When you say "get", how are you getting? Are you using a query? If
so,
please post the SQL statement.

We aren't there, we can't see how you are inputting "01/27/2008", nor
where.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm using a form for a from/to date for users. I was using the
calendar
control but I ended up with some bizarre "froms" and "tos". I set
the
"from"
to 1/20/2008 and got all the records from that date from 7:52 AM
on....but
I
was missing seven records from 12:27 AM to the aforementioned date.
The
"to"
gave me half the records for the date 01//26/2008 but didn't give me
any
records past 3:29 PM.

I switched from the calender tool and put in a text box and now the
user
types in the date, but in order for me to get 0/26/2008 records, I
have
to
input 01/27/2008. That gives me all of 1/26 records and none of
1/27.

BTW, I'm using the short date format.

Thanx!

I think I had this problem before but something is getting real
weird
here.
Any clues as to what's happening?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top