MS-ACCESS query date problem + date formatting in datagrid 
Author Message
 MS-ACCESS query date problem + date formatting in datagrid

Hi everybody,

I'm having problems with an access-query that I want to load into a dataset
to bind to a datagrid.  The query needs to get some records that were added
in a certain period.  I used the between function in my Query but the output
us not correct.

This is the query:
SELECT Magazijnbon.Doel, Magazijnbon.[Ingave datum],
Magazijnbon.Leveringsdatum, Incidenten.IncidentRequestNummer,
Incidenten.IncidentStartUur, Incidenten.IncidentEindUur FROM Magazijnbon
INNER JOIN Incidenten ON Magazijnbon.LeverID = Incidenten.IncidentRequestID
WHERE (((Magazijnbon.Doel) Like 'Centraal Incident') AND
((Magazijnbon.Leveringsdatum) Is Not Null And ((Magazijnbon.Leveringsdatum)
between #01/08/2002# And #31/08/2002#) And Incidenten.IncidentEindUur Is Not
Null)) ORDER BY Magazijnbon.[Ingave datum];

My datagrid shows records from july too !!!  When I copy-paste it in the
MSACCESS querydesigner and run it, the output is correct though.

Is there some issue that I don't now about ?  Could this be a problem
because .NET treats dates differently than MSACCESS ?

Also, as you probably know there is only one field-type for Time and Dates
(Date/Time)in MSACCESS.  There is another field in this query that holds a
time.  I defined it in the access-table as Date/Time with the short time
notation.  But when the query shows it's data in the datagrid all these
times are converted to the underlying format (like 30/12/1899).  How can I
let the fields that hold these time-values display correctly (ex.: 22:13)?

Any help would be greatly appreciated,

Tom



Fri, 04 Feb 2005 21:25:54 GMT  
 MS-ACCESS query date problem + date formatting in datagrid
This is a problem that can be better handled in a MS-Access newsgroup.

Your problem lies in how VBA handles dates.  VBA only handles dates in the
MM/DD/YY format.  You will need to convert your dates to this format for
processing in VBA.

01/08/2002 translates to January 8, 2002
31/08/2002 translates to August 31, 2002 but can sometimes give you
something completely different.

So you are searching for all records between January and September not just
August.

Michael


Quote:
> Hi everybody,

> I'm having problems with an access-query that I want to load into a
dataset
> to bind to a datagrid.  The query needs to get some records that were
added
> in a certain period.  I used the between function in my Query but the
output
> us not correct.

> This is the query:
> SELECT Magazijnbon.Doel, Magazijnbon.[Ingave datum],
> Magazijnbon.Leveringsdatum, Incidenten.IncidentRequestNummer,
> Incidenten.IncidentStartUur, Incidenten.IncidentEindUur FROM Magazijnbon
> INNER JOIN Incidenten ON Magazijnbon.LeverID =

Incidenten.IncidentRequestID
Quote:
> WHERE (((Magazijnbon.Doel) Like 'Centraal Incident') AND
> ((Magazijnbon.Leveringsdatum) Is Not Null And

((Magazijnbon.Leveringsdatum)
Quote:
> between #01/08/2002# And #31/08/2002#) And Incidenten.IncidentEindUur Is
Not
> Null)) ORDER BY Magazijnbon.[Ingave datum];

> My datagrid shows records from july too !!!  When I copy-paste it in the
> MSACCESS querydesigner and run it, the output is correct though.

> Is there some issue that I don't now about ?  Could this be a problem
> because .NET treats dates differently than MSACCESS ?

> Also, as you probably know there is only one field-type for Time and Dates
> (Date/Time)in MSACCESS.  There is another field in this query that holds a
> time.  I defined it in the access-table as Date/Time with the short time
> notation.  But when the query shows it's data in the datagrid all these
> times are converted to the underlying format (like 30/12/1899).  How can I
> let the fields that hold these time-values display correctly (ex.: 22:13)?

> Any help would be greatly appreciated,

> Tom



Fri, 04 Feb 2005 23:46:51 GMT  
 MS-ACCESS query date problem + date formatting in datagrid
TomVdB,
    This may be caused by the query misinterpeting the date values and using
MM/dd/yyyy format incorrectly for your locale.
Ron Allen

Quote:
> Hi everybody,

> I'm having problems with an access-query that I want to load into a
dataset
> to bind to a datagrid.  The query needs to get some records that were
added
> in a certain period.  I used the between function in my Query but the
output
> us not correct.

> This is the query:
> SELECT Magazijnbon.Doel, Magazijnbon.[Ingave datum],
> Magazijnbon.Leveringsdatum, Incidenten.IncidentRequestNummer,
> Incidenten.IncidentStartUur, Incidenten.IncidentEindUur FROM Magazijnbon
> INNER JOIN Incidenten ON Magazijnbon.LeverID =

Incidenten.IncidentRequestID
Quote:
> WHERE (((Magazijnbon.Doel) Like 'Centraal Incident') AND
> ((Magazijnbon.Leveringsdatum) Is Not Null And

((Magazijnbon.Leveringsdatum)
Quote:
> between #01/08/2002# And #31/08/2002#) And Incidenten.IncidentEindUur Is
Not
> Null)) ORDER BY Magazijnbon.[Ingave datum];

> My datagrid shows records from july too !!!  When I copy-paste it in the
> MSACCESS querydesigner and run it, the output is correct though.

> Is there some issue that I don't now about ?  Could this be a problem
> because .NET treats dates differently than MSACCESS ?

> Also, as you probably know there is only one field-type for Time and Dates
> (Date/Time)in MSACCESS.  There is another field in this query that holds a
> time.  I defined it in the access-table as Date/Time with the short time
> notation.  But when the query shows it's data in the datagrid all these
> times are converted to the underlying format (like 30/12/1899).  How can I
> let the fields that hold these time-values display correctly (ex.: 22:13)?

> Any help would be greatly appreciated,

> Tom



Fri, 04 Feb 2005 23:52:54 GMT  
 MS-ACCESS query date problem + date formatting in datagrid
specify the date in dd/mmm/yyyy format to remove any doub  about
interpretation of month

Colin


Quote:
> Hi everybody,

> I'm having problems with an access-query that I want to load into a
dataset
> to bind to a datagrid.  The query needs to get some records that were
added
> in a certain period.  I used the between function in my Query but the
output
> us not correct.

> This is the query:
> SELECT Magazijnbon.Doel, Magazijnbon.[Ingave datum],
> Magazijnbon.Leveringsdatum, Incidenten.IncidentRequestNummer,
> Incidenten.IncidentStartUur, Incidenten.IncidentEindUur FROM Magazijnbon
> INNER JOIN Incidenten ON Magazijnbon.LeverID =

Incidenten.IncidentRequestID
Quote:
> WHERE (((Magazijnbon.Doel) Like 'Centraal Incident') AND
> ((Magazijnbon.Leveringsdatum) Is Not Null And

((Magazijnbon.Leveringsdatum)
Quote:
> between #01/08/2002# And #31/08/2002#) And Incidenten.IncidentEindUur Is
Not
> Null)) ORDER BY Magazijnbon.[Ingave datum];

> My datagrid shows records from july too !!!  When I copy-paste it in the
> MSACCESS querydesigner and run it, the output is correct though.

> Is there some issue that I don't now about ?  Could this be a problem
> because .NET treats dates differently than MSACCESS ?

> Also, as you probably know there is only one field-type for Time and Dates
> (Date/Time)in MSACCESS.  There is another field in this query that holds a
> time.  I defined it in the access-table as Date/Time with the short time
> notation.  But when the query shows it's data in the datagrid all these
> times are converted to the underlying format (like 30/12/1899).  How can I
> let the fields that hold these time-values display correctly (ex.: 22:13)?

> Any help would be greatly appreciated,

> Tom



Sat, 05 Feb 2005 05:23:13 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. ms-access - delete old dates and change dates

2. How do I store an empty date on a date field (MS Access)

3. unrar.dll ms dos date time format problems

4. problems with VB date --> Access date

5. Problem with Queries on dates in VB (Access)

6. Problem built in parameterised Access queries and dates

7. problem with Format and a user defined date format

8. My datagrid cannot display full date and time (only date is shown)

9. Date formatting of date type

10. DataGrid: Turning off auto date formatting?

11. Format a date in datagrid?

12. searching a routine to convert date number in normal format date

 

 
Powered by phpBB® Forum Software