Cannot select Sunday only records in WHERE statement

[KavJack]

My query qryGDATE3 won't work.
I get message "Data type mismatch in criteria expression" on execution.


qryGDATE2 does work correctly.
I just want to select all the results that come up with a DOW equal to "Sunday".
How can I make this work ?

Query - qryGDATE1
SELECT N_League.*
FROM N_League
WHERE GDATE>0 AND GDATE-10000*INT(GDATE/10000)>0;

Query - qryGDATE2

SELECT qryGDATE1.*, FuncWkd(qryGDATE1.GDATE) AS DOW
FROM qryGDATE1
ORDER BY SEASON, GDATE;

Query - qryGDATE3

SELECT qryGDATE2.*, qryGDATE2.DOW
FROM qryGDATE2
WHERE (((qryGDATE2.DOW)="Sunday"));


Function FuncWkd(InDate As Long) As String

Dim IntX As Long
Dim GDATE As Date
Dim Day As Long
Dim Month As Long
Dim Year As Long

Year = InDate / 10000
Month = (InDate - (Year * 10000)) / 100
If (Month = 0) Then GoTo Err:

Day = (InDate - ((Year * 10000) + (Month * 100)))
GDATE = Str(Month) & "/" & Str(Day) & "/" & Str(Year)
IntX = Weekday(GDATE)
If (IntX = 0) Then IntX = 7
FuncWkd = WeekdayName(IntX)

On Error GoTo Err:


Exit Function
Err:
FuncWkd = "No Date"
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description


Exit Function
End Function

My GDATE field has a data type of double integer, i.e a date such as 7th Dec. 1977 is held as the number 19771207

CStr function didn't work

SELECT qryGDATE2.*, qryGDATE2.DOW
FROM qryGDATE2
WHERE CStr(qryGDATE2.DOW)=CStr("Sunday");

 

[dhookom]

You are attempting to compare two string values so I'm not sure why you need to use CStr(). Why not just use:
[tt][blue]WHERE qryGDATE2.DOW="Sunday";[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[KavJack]

If you look further up in my post I did do that and it gives me the error message "Data type mismatch in criteria expression" on execution. Someone on another board suggested that I use the CStr function. It gives the same error message. I included that in my statement so that no-one else would suggest that again.

 

[dhookom]

Are all values in GDATE truly convertable to dates? Also,

Dim Day As Long
Dim Month As Long
Dim Year As Long

Each of these variables is a reserved word. They shouldn't be used as variable names.



Duane
Hook'D on Access
MS Access MVP

 

[KavJack]

Are all values in GDATE truly convertable to dates?

No, some are null values.

Each of these variables is a reserved word. They shouldn't be used as variable names.

Good point. Nobody else mentioned that. I have changed them
Day to Lday, etc.

 

[KavJack]

I misspoke in my last post. GDATE does not contain null values since qryGDATE1 eliminates the null type dates.
GDATE ranges from 19680810 to 20080426.

 

[Golom]

In qryGDATE3 use

SELECT qryGDATE2.*
FROM qryGDATE2
WHERE qryGDATE2.DOW = "Sunday";

instead of

SELECT qryGDATE2.*, qryGDATE2.DOW
FROM qryGDATE2
WHERE qryGDATE2.DOW = "Sunday";

With your version you will actually have two copies of the DOW field and the names will not be what they appear to be in the SELECT.

They will be called "qryGDATE2.DOW" (which is the field name and not a Table.Field notation) and "Field0".

 

[KavJack]

Sorry but this suggested qryGDATE3 code did not work giving the same error as before "Data type mismatch in criteria expression

 

[Golom]

Your FuncWkd doesn't catch all possible date problems. Try it with "20087725" or "2008525" for example and you get a type mismatch when it hits

GDATE = Str(Month) & "/" & Str(Day) & "/" & Str(Year)

Try this instead

Function FuncWkd(InDate As Long) As String
    Dim GDATE                       As Date
    Dim Dy                          As Long
    Dim Mn                          As Long
    Dim Yr                          As Long
    Dim strDate                     As String
    
    strDate = Format(InDate, "0")
    If Len(strDate) <> 8 Then
        FuncWkd = strDate & " is invalid"
        Exit Function
    End If
    
    Yr = Left$(strDate, 4)
    Mn = Mid$(strDate, 5, 2)
    Dy = Right$(strDate, 2)
    On Error GoTo Err
    GDATE = CDate(Yr & "-" & Mn & "-" & Dy)
    
    FuncWkd = WeekdayName(Weekday(GDATE))
    Exit Function
Err:
    FuncWkd = Yr & "-" & Mn & "-" & Dy & " is an invalid Date"
End Function

 

[KavJack]

I agree that your code is more comprehensive than mine but it still gives me the same error message "Data type mismatch in criteria expression" on execution.
Actually my database does not have any dates like the examples you gave. My date Field GDATE is either empty (null) or it contains 8 numeric digits. I enter this date field manually and if I don't enter the correct number of digits I get an error right away.

 

[dhookom]

I would try short-circuit the function with something like

'...
   FuncWkd = "Sunday"
 Exit Function
'....

This would tell you if the result returned from the function is causing the issue.

Duane
Hook'D on Access
MS Access MVP

 

[KavJack]

I short circuited the Function so that it always returned "Sunday" and I still got the same error. I believe that the Function is not the problem. Somehow in the WHERE statement Access is not recognising either side of the = sign to be of the same data type. I have tried many different way to get around this including the Len function and I can never overcome this error.

 

[dhookom]

Change your first query to:

Query - qryGDATE1
SELECT N_League.*
FROM N_League
WHERE Nz(GDATE,0)>0 AND Nz(GDATE,0)-10000*INT(Nz(GDATE,0)/10000) >0;

Duane
Hook'D on Access
MS Access MVP

 

[KavJack]

I inserted the Nz function into qryGDATE1 as directed and I still got the the same error message "Data type mismatch in criteria expression" on execution.

 

[dhookom]

I would take out the criteria from the last query and set an order by and test.

Also, you mentioned [red]double integer[/red] when there really isn't a datatype like this.

Duane
Hook'D on Access
MS Access MVP

 

[KavJack]

double integer ----
when I go to the Table Design View and select the field GDATE it shows a field size of double which I assumed meant double integer. I changed the field size to long integer and got the same error message.


"I would take out the criteria from the last query and set an order by and test. " I don't know to which query you are referring.

 

[dhookom]

Query - qryGDATE3


Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Double" is a double-word floating point data type. "Long" is an integer (i.e. whole number) data type. For your purposes, either should work.

Just as another test, Try this query

SELECT *, FuncWkd(GDATE) As [DOW]
FROM   N_League
WHERE  GDATE IS NOT NULL 
  And  FuncWkd(GDATE) = "Sunday"
ORDER BY SEASON, GDATE

and use the following for FuncWkd

Function FuncWkd(InDate As Variant) As String
    Dim GDate                       As Date
    Dim strDate                     As String
    
    If IsNull(InDate) Then
        FuncWkd = "Argument is NULL"
        Exit Function
    End If
    
    If Not IsNumeric(InDate) Then
        FuncWkd = "'" & InDate & "' is invalid"
        Exit Function
    End If
    
    strDate = Format(InDate, "####-##-##")
    If Len(strDate) <> 10 Then
        FuncWkd = "'" & InDate & "' is invalid"
        Exit Function
    End If
    
    On Error GoTo Err
    GDate = CDate(strDate)
    FuncWkd = WeekdayName(Weekday(GDate))
    Exit Function
Err:
    FuncWkd = "'" & strDate & "' is an invalid Date"
End Function

You might also check that in Tools / Options / General when viewing VB code, you have "Break on Unhandled Errors" selected.

 

[KavJack]

Yes, that last construct worked. Do you think that you know why our earlier efforts didn't ? If I learn I can avoid coding that way in the future.

 

[Golom]

Wish I did!

Did you need to reset "Break on Unhandled Errors"?
Was it set to "Break on All Errors" or "Break in Class Module"?

It's probable that we could figure it out if we saw the data but the SQL ... although you obviously don't need stacked queries to get the result you wanted ... looks correct. Just a bit cumbersome for the task at hand.