Date Problem

[aziz rasul]

I have the following SQL statement: -

SELECT PIMSADM_MHMDS_OPATT_E.PTID AS OPATT, PIMSADM_MHMDS_OPATT_E.EVENTDATE, Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5) AS ND
FROM PIMSADM_MHMDS_OPATT_E
WHERE (((PIMSADM_MHMDS_OPATT_E.PTID)="E1472643") AND ((Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5))>=#4/1/2005#));

[EVENTDATE] is of date/time data type in a Oracle linked table. Typical example of an entry in [EVENTDATE] is 17/02/2005 11:30:00 when you place the mouse cursor in a cell. In appearance you only see 17/02/2005.

When I run this query, I get dates that include year values of 2003.

I have also tried the criteria as "4/1/2005" with no luck. Also tried

SELECT PIMSADM_MHMDS_OPATT_E.PTID AS OPATT, PIMSADM_MHMDS_OPATT_E.EVENTDATE, CDate(Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5)) AS ND
FROM PIMSADM_MHMDS_OPATT_E
WHERE (((PIMSADM_MHMDS_OPATT_E.PTID)="E1472643") AND ((CDate(Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5)))>=#4/1/2005#));

Can anyone help. Desparate to get this done.

 

[Jon K]

Try this:

SELECT [PIMSADM_MHMDS_OPATT_E].[PTID] AS OPATT, [PIMSADM_MHMDS_OPATT_E].[EVENTDATE], DateValue([EVENTDATE]) AS DN
FROM PIMSADM_MHMDS_OPATT_E
WHERE PIMSADM_MHMDS_OPATT_E.PTID="E1472643" and DateValue([EVENTDATE])>=#4/1/2005#;


Note
In VBA code and query SQL statements, the date between the # signs must be put in US format. So #4/1/2005# means April 1, 2005.
.

 

[aziz rasul]

Didn't work. Still gave me 2003 dates.

I'm using a select query rather than VBA code, but noted your point.

 

[Summerwind]

Not sure why you are getting 2003 dates, but strange things can happen with dates - both software and girlie types!

Whenever I have problems with dates I find that converting them to DateSerial format usually solves the problem. I think this is due to the way the lower level software manipulates dates that aren't in US format.

 

[checoturco]

i´m not sure but try this

Dim dfini As String
Dim mfini As String
Dim yfini As String

Dim dtdate As Date

dini = Mid([EVENTDATE], 1, 2)
mini = Mid([EVENTDATE], 4, 2)
yini = Mid([EVENTDATE], 7, 4)


dtdate = mini & " / " & dini & " / " & yini

SELECT PIMSADM_MHMDS_OPATT_E.PTID AS OPATT, PIMSADM_MHMDS_OPATT_E.EVENTDATE, Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5) AS ND
FROM PIMSADM_MHMDS_OPATT_E
WHERE (((PIMSADM_MHMDS_OPATT_E.PTID)="E1472643") AND (dtdate>=#4/1/2005#);

 

[aziz rasul]

Do I place this in a module? If so how do I use the SQL statement in the query?

 

[Bat17]

What is event date? If it is a date field then you dont need to chop it up. If it is a text field then you need to turn it into a date to let you criteria work with it

Peter

 

[aziz rasul]

EVENTDATE is a DateTime data type in the Oracle table.

 

[Bat17]

WHERE (((PIMSADM_MHMDS_OPATT_E.PTID)="E1472643") AND ([dtdate] >=#4/1/2005#);
should work then, bearing in mind that 4/1/2005 is Apr 1st

Peter

 

[checoturco]

asis razul, i think i´m wrong in my last post, you don´t need declare the variables, try the code above but can you put here the output of your first query.

SELECT PIMSADM_MHMDS_OPATT_E.PTID AS OPATT, PIMSADM_MHMDS_OPATT_E.EVENTDATE, Left([EVENTDATE],2) & Mid([EVENTDATE],3,3) & Mid([EVENTDATE],6,5) AS ND
FROM PIMSADM_MHMDS_OPATT_E
WHERE (((PIMSADM_MHMDS_OPATT_E.PTID)="E1472643") AND ((Mid([EVENTDATE], 4, 2) & " / " & Mid([EVENTDATE], 1, 2) & " / " & Mid([EVENTDATE], 7, 4))>=#4/1/2005#);

checo