Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    Unanswered: Simple SQL Statement

    Hey,
    This is a simple question, what is wrong with the syntax of this select statement?

    SELECT [icbc Date/Period].Day,Count([icbc Date/Period].PassTime), [icbc Date/Period].Period
    FROM [icbc Date/Period]
    Group By [icbc Date/Period].Day, [icbc Date/Period].Period;

    I get "Datatype mismatch in criteria statement" error message on this statement", This select statement is based on another query. The other query breaks up some date/time fields into their own columns. ie/ column1 = date, column2 = time, etc...The other query works fine.


    Can anyone help?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try putting square brackets around column names Day, etc. which are reserved words

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Jul 2002
    Posts
    30

    SQL Statement

    SELECT [icbc Date/Period].[Day],Count([icbc Date/Period].[PassTime]) As CountOfPassTime, [icbc Date/Period].[Period]
    FROM [icbc Date/Period]
    Group By [icbc Date/Period].[Day], [icbc Date/Period].[Period];


    This is teh select statement now, and I still get "Data type mismatch in criteria statement...???

  4. #4
    Join Date
    Jul 2002
    Posts
    30

    SQL

    SELECT CStr( [icbc Date/Period].Day),Count(CStr([icbc Date/Period].[PassTime])) As CountOfPassTime, CStr([icbc Date/Period].Period)
    FROM [icbc Date/Period]
    Group By [icbc Date/Period].Day, [icbc Date/Period].Period;

    Here's what I have modified it to after checking out the error message on MSDN...Still same error though...I think this should be right, I have just written it incorectly....Would love your feedback.
    Thanks S

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does the [icbc Date/Period] query look like?

  6. #6
    Join Date
    Jul 2002
    Posts
    30
    SELECT DatePart("d",[SpeedTime]) AS [Day], TimeValue([SpeedTime]) AS PassTime, IIf([NewPlate] Like "R ","VOID",IIf([PassTime] Like "*AM*" And [PassTime] Between "6:30" And "9:30","AM",IIf([PassTime] Like "*AM*" And [PassTime] Between "11:00" And "12:00","MID",IIf([PassTime] Like "*PM*" And [PassTime] Between "0:00" And "2:00","MID",IIf([PassTime] Like "*PM*" And [PassTime] Between "3:30" And "6:30","PM","VOID"))))) AS Period, icbcmerge.SpeedTime, icbcmerge.SpeedValid, icbcmerge.Speed, icbcmerge.Accel, icbcmerge.VIN, icbcmerge.REGI, icbcmerge.NewPlate, icbcmerge.VTYPE, IIf([NewPlate] Like "####[a-z][a-z]*","Commercial",IIf([NewPlate] Like " " Or [NewPlate] Like "HD " Or [NewPlate] Like "HDT " Or [NewPlate] Like "HDV " Or [NewPlate] Like "LD " Or [NewPlate] Like "LDT " Or [NewPlate] Like "LDV " Or [NewPlate] Like "MC " Or [NewPlate] Like "R ","Unknown","Private")) AS RegClass, IIf([VTYPE]="",IIf([NewPlate]="HD " Or [NewPlate]="HDT " Or [NewPlate]="HDV " Or [NewPlate]="LD " Or [NewPlate]="LDT " Or [NewPlate]="LDV " Or [NewPlate]="MC " Or [NewPlate]="R ",[NewPlate],"Unknown"),[VTYPE]) AS [Vehicle Type]
    FROM icbcmerge;

  7. #7
    Join Date
    Jul 2002
    Posts
    30
    Ok, I checked out the data returned for the above query and there was data returned represented by #error, so that explains that. I then went to the table they are based on and discovered that after several thousand records or so, the three fields with data/time datatype actually had no values in them. So, finally I have a more concrete question....If I have null values in these fields, I would have to modify both of my querys to evaluate nulls ina certain manner...I do not know what the syntax would be to do this...Do you?? The columns in question are the first three fields in both the queries I posted...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use ISNULL, e.g. select iif(isnull(foo),'void',foo)

    by the way, you use TimeValue(SpeedTime) as though you wanted to pull the time portion out of the SpeedTime datetime value -- TimeValue is actually a function which accepts a string and tries to turn it into a time value, so i think you may want to use DatePart instead

    rudy

  9. #9
    Join Date
    Jul 2002
    Posts
    30
    Thanks alot, I really appreciate your help!
    All the best,
    S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •