Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Create Table problem

    OK, I am getting a "Datatype mismatch in criteria expression" runtime error when I try to create a temp table from a query. The only thing I can see is the date format. I am using the Date and time Picker control on my form, and am wondering if the time at the end is a problem. When I use debug.print, the date prints as follows:

    Code:
     
    8/15/2008 12:41:56 PM
    The entire SQL statement prints as follows:
    Code:
     
    SELECT OBDATA.* INTO REPORT FROM OBDATA WHERE (((OBDATA.SITENAME)= 'AFNI') AND ((OBDATA.PROJECT)='Winback') AND ((OBDATA.OBDATE) Between '8/15/2008 12:41:56 PM' And '8/18/2008 3:06:05 AM'));

    My Code is below, and as usual, I appreciate your help!
    Code:
     
    Dim strSITE As String, strPROJECT As String
    Dim dteSTART As Date, dteEND As Date
    Dim intYES As Integer, intNO As Integer, intNA As Integer, intOBS As Integer
    Dim intQ As Integer, intASCORE As Integer, intAPP As Integer
    Dim i As Variant
    Dim strSQL As String
    Dim db As DAO.Database
     
    '***Get the criteria for the query
        strSITE = Nz(Me.SITENAME, "")
        strPROJECT = Nz(Me.PROJECT, "")
        dteSTART = Nz(Me.dpSTART, "")
        dteEND = Nz(Me.dpEND, "")
    
    '***Insert the data into the temp table
        strSQL = "SELECT OBDATA.* INTO REPORT FROM OBDATA "
        strSQL = strSQL & "WHERE (((OBDATA.SITENAME)= '" & strSITE & "') AND ((OBDATA.PROJECT)='" & strPROJECT & "') AND ((OBDATA.OBDATE) Between '" & dteSTART & "' And '" & dteEND & "'));"
        Set db = CurrentDb
        db.Execute strSQL, dbFailOnError
    
        '***Test to see if the row was written
        If db.RecordsAffected = 1 Then
            'No Need to notify for success here
        ElseIf db.RecordsAffected <> 1 Then
            MsgBox "Serious Error, the row was not written to the DB"
            Exit Sub
        End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the value coming back from the date time picker is a datetime alue tne it should be K
    however I note you are not encapulsating date values in the hash (#) symbol

    have you checked that the data types in obdata and report are identical, checked that report can handle nulls.

    incidentally Id be careful calling a table report.. Access/JET may well throw a wobbler on that.. I think its a reserved word

    mind you I'd love to know why you are creating a temporary table form data form a single other table.... seems a bit flaky to me, but no doubt you have a good reason for doing that.

    how are you handling the event where he tamporary table may already exist
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2007
    Posts
    74

    Maybe there is a better way

    Flaky is my middle name...

    HAHA

    The temp table is just the way I started coding. I would rather find a better way,

    At this point, I need to separate the data in order to get what I need for the report. I am looping a dcount to go through all 33 columns in my temp data as follows:

    Code:
    '***Loop through for Yes/No/NA totals
        intYES = 0
        intNO = 0
        intNA = 0
        
        For i = 1 To 33
        
            intYES = intYES + Nz(DCount("[" & i & "]", "[REPORT]", "[" & i & "] = 1"), "")
            intNO = intNO + Nz(DCount("[" & i & "]", "[REPORT]", "[" & i & "] = 2"), "")
            intNA = intNA + Nz(DCount("[" & i & "]", "[REPORT]", "[" & i & "] = 3"), "")
        
        Next i
    I tried the following but got a type mismatch error :
    Code:
     
     
    intYES = intYES + Nz(DCount("[" & i & "]", "[OBDATA]", "[" & i & "] = 1" And "[OBDATE] BETWEEN #" & dteSTART & "# AND #" & dteEND & "#"), "")
    This, I think would be the best way if it will work because I can use the data in the table as is without having to use a tmp table or a recordset.

    So, do you you see what the type mismatch error might be?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'm getting a "type mismatch" error when I read post #2 ^.^

    I think the problem is with your encapsulation of your dates in single quotes. Jet needs them to be hash symbols (#).

    strSQL = strSQL & "WHERE (((OBDATA.SITENAME)= '" & strSITE & "') AND ((OBDATA.PROJECT)='" & strPROJECT & "') AND ((OBDATA.OBDATE) Between '" & dteSTART & "' And '" & dteEND & "'));"


    Should be

    strSQL = strSQL & "WHERE (((OBDATA.SITENAME)= '" & strSITE & "') AND ((OBDATA.PROJECT)='" & strPROJECT & "') AND ((OBDATA.OBDATE) Between #" & dteSTART & "# And #" & dteEND & "#));"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brent Blevins
    I would rather find a better way
    In that case I would normalise your table if I were you - the query is then trivial

    Assuming you don't, rather than 99 DCounts (very, very slow) another option is a single query with 100+ fields:
    Code:
    SELECT mypk, SUM(SWITCH([1] = 1, 1)) AS [1_yes], SUM(SWITCH([1] = 2, 1)) AS [1_no], SUM(SWITCH([1] = 3, 1)) AS [1_na], ....., SUM(SWITCH([33] = 1, 1)) AS [33_yes], SUM(SWITCH([33] = 2, 1)) AS [33_no], ....
    FROM myTable
    GROUP BY mypk
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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