use cdate in a query?

[Co]

Hi All,

I'm trying to create a query with the following:

strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).

the field expires is a textfield and not a datefield.
Is there any way to do this?

Regards
Marco

 

[June7 via AccessMonster.com]

How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"

Hi All,

I'm trying to create a query with the following:

strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).

the field expires is a textfield and not a datefield.
Is there any way to do this?

Regards
Marco

 

[Co]

How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"

Hi All,

I'm trying to create a query with the following:

strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).

the field expires is a textfield and not a datefield.
Is there any way to do this?

Regards
Marco

Yes,

the variablen are textboxes.
I changed the code to:

strFilter & "CDate([expires]) BETWEEN " & Chr(35) & tbVerlooptVan.Text
& Chr(35) & " AND " & Chr(35) & tbVerlooptTot.Text & Chr(35)

Dim sql As String = "SELECT Bestanden.*, Kabinet.foldername FROM
Bestanden INNER JOIN " & _
"Kabinet ON Bestanden.lokatie = Kabinet.Id
WHERE " & sValues

And now I'm getting an error at the second record saying:

There's a type mismatch in the expression of the criteria.

Marco

 

[John Spencer]

If EXPIRES is ever null you will get an error generated by CDate. The
error value being returned will then cause a type mismatch error.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"

Hi All,
I'm trying to create a query with the following:
strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).
the field expires is a textfield and not a datefield.
Is there any way to do this?
Regards
Marco

Yes,

the variablen are textboxes.
I changed the code to:

strFilter & "CDate([expires]) BETWEEN " & Chr(35) & tbVerlooptVan.Text
& Chr(35) & " AND " & Chr(35) & tbVerlooptTot.Text & Chr(35)

Dim sql As String = "SELECT Bestanden.*, Kabinet.foldername FROM
Bestanden INNER JOIN " & _
"Kabinet ON Bestanden.lokatie = Kabinet.Id
WHERE " & sValues

And now I'm getting an error at the second record saying:

There's a type mismatch in the expression of the criteria.

Marco

 

[Co]

If EXPIRES is ever null you will get an error generated by CDate.  The
error value being returned will then cause a type mismatch error.

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"
Co wrote:
Hi All,
I'm trying to create a query with the following:
strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).
the field expires is a textfield and not a datefield.
Is there any way to do this?
Regards
Marco

Yes,

the variablen are textboxes.
I changed the code to:

strFilter & "CDate([expires]) BETWEEN " & Chr(35) & tbVerlooptVan.Text
& Chr(35) & " AND " & Chr(35) & tbVerlooptTot.Text & Chr(35)

Dim sql As String = "SELECT Bestanden.*, Kabinet.foldername FROM
Bestanden INNER JOIN " & _
                            "Kabinet ON Bestanden.lokatie = Kabinet.Id
WHERE " & sValues

And now I'm getting an error at the second record saying:

There's a type mismatch in the expression of the criteria.

Marco

Yes I guessed that was the case.
How to prevent this?

Marco

 

[John Spencer]

One method would be to test Expires and see if it can be interpreted as
a date.

IIF(IsDate([Expires]),CDate(Expires),Null) = ...

Another way would be to base the query on a query. In the first query
you eliminate records where EXPIRES is NULL and then used that query in
place of the table in your current query.

This second approach MAY be more efficient depending on the size of your
table and the percentage of records where Expires is Null.

Yet another approach, if you are sure EXPIRES always contains a valid
date string or contains null is to use the NZ function and pass a date
you know will be out of range.

CDate(NZ([Expires],"3999-12-31"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

If EXPIRES is ever null you will get an error generated by CDate. The
error value being returned will then cause a type mismatch error.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"
Co wrote:
Hi All,
I'm trying to create a query with the following:
strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).
the field expires is a textfield and not a datefield.
Is there any way to do this?
Regards
Marco
--
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200905/1
Yes,
the variablen are textboxes.
I changed the code to:
strFilter & "CDate([expires]) BETWEEN " & Chr(35) & tbVerlooptVan.Text
& Chr(35) & " AND " & Chr(35) & tbVerlooptTot.Text & Chr(35)
Dim sql As String = "SELECT Bestanden.*, Kabinet.foldername FROM
Bestanden INNER JOIN " & _
"Kabinet ON Bestanden.lokatie = Kabinet.Id
WHERE " & sValues
And now I'm getting an error at the second record saying:
There's a type mismatch in the expression of the criteria.
Marco

Yes I guessed that was the case.
How to prevent this?

Marco

 

[Co]

One method would be to test Expires and see if it can be interpreted as
a date.

IIF(IsDate([Expires]),CDate(Expires),Null) = ...

Another way would be to base the query on a query.  In the first query
you eliminate records where EXPIRES is NULL and then used that query in
place of the table in your current query.

This second approach MAY be more efficient depending on the size of your
table and the percentage of records where Expires is Null.

Yet another approach, if you are sure EXPIRES always contains a valid
date string or contains null is to use the NZ function and pass a date
you know will be out of range.

CDate(NZ([Expires],"3999-12-31"))

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

If EXPIRES is ever null you will get an error generated by CDate.  The
error value being returned will then cause a type mismatch error.
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
Co wrote:
How do you plan to use this string, in form's filter property? Are the
variables textboxes on a form holding a date values?
"CDate([expires]) BETWEEN #" & tbGemaaktVan.Text & "# AND #" & tbGemaaktTot.
Text "#"
Co wrote:
Hi All,
I'm trying to create a query with the following:
strFilter = "CDate([expires])>" & Chr(35) & tbGemaaktVan.Text & Chr
(35) & " AND CDate([expires])<" & Chr(35) & tbGemaaktTot.Text & Chr
(35).
the field expires is a textfield and not a datefield.
Is there any way to do this?
Regards
Marco
--
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200905/1
Yes,
the variablen are textboxes.
I changed the code to:
strFilter & "CDate([expires]) BETWEEN " & Chr(35) & tbVerlooptVan.Text
& Chr(35) & " AND " & Chr(35) & tbVerlooptTot.Text & Chr(35)
Dim sql As String = "SELECT Bestanden.*, Kabinet.foldername FROM
Bestanden INNER JOIN " & _
                            "Kabinet ON Bestanden.lokatie = Kabinet.Id
WHERE " & sValues
And now I'm getting an error at the second record saying:
There's a type mismatch in the expression of the criteria.
Marco

Yes I guessed that was the case.
How to prevent this?

Marco

Thanks John,

The first worked for me.

Marco