Results 1 to 13 of 13
  1. #1
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296

    Can't figure out whats wrong with this WHERE clause

    Here is my vba
    Code:
    UpdateSQL = "UPDATE dbo.JT_DailyTimeEntry SET EndTime = ""0000"", Overtime = ""N"", NewWTStatus = ""COM"", NewStatusComment = ""T/T COMPLETED"", NewStatusDate = '" & CurrentDate & "'," & _
    " ActivityCode = """ & ActCode & """, WTPostWTStep = """ & StepNum & """, DepartmentWorkedIn = ""00"", WTInHistory = ""N"", EarningsCode = ""000001"", LaborBillingType = ""N""," & _
    " SeqNoLDTrxRecord = """ & LDSequenceNo & """, HoursWorked = 0, PRHourstoPost = 0, QuantityCompleted = 0, LaborCost = 0, BurdenCost = 0, DilutedTime = 0, OverheadCost = 0," & _
    " DateCreated = '" & CurrentDate & "', TimeCreated = """ & CurrentTime() & """, UserCreatedKey = """ & EmployeeNo & """, DateUpdated = '" & CurrentDate & "', TimeUpdated = """ & CurrentTime() & """, UserUpdatedKey = """ & EmployeeNo & """" & _
    " WHERE ((TransactionDate = '" & CurrentDate & "') AND (DepartmentNo = ""00"") AND (EmployeeNo = """ & EmployeeNo & """) AND (RecordType = ""1"") AND (SalesOrderNo = """ & SONum & """) AND (WTNumber = """ & DHRNum & """) AND (WTStep = """ & StepNum & """) AND (SequenceNo = """ & sSequenceNo & """));"
    Here is the debug.print of it as SQL



    Code:
    UPDATE dbo.JT_DailyTimeEntry SET EndTime = "0000", Overtime = "N", NewWTStatus = "COM", NewStatusComment = "T/T COMPLETED", NewStatusDate = '', ActivityCode = "", WTPostWTStep = "", DepartmentWorkedIn = "00", WTInHistory = "N", EarningsCode = "000001", LaborBillingType = "N", SeqNoLDTrxRecord = "", HoursWorked = 0, PRHourstoPost = 0, QuantityCompleted = 0, LaborCost = 0, BurdenCost = 0, DilutedTime = 0, OverheadCost = 0, DateCreated = '', TimeCreated = "9.05976", UserCreatedKey = "", DateUpdated = '', TimeUpdated = "9.05976", UserUpdatedKey = "" WHERE ((TransactionDate = '') AND (DepartmentNo = "00") AND (EmployeeNo = "") AND (RecordType = "1") AND (SalesOrderNo = "") AND (WTNumber = "") AND (WTStep = "") AND (SequenceNo = ""));
    I keep getting the following error for each of the WHERE clauses except for the TransactionDate.
    Code:
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '00'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'EmployeeNo'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name '1'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SalesOrderNo'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'WTNumber'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'WTStep'.
    (207): [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SequenceNo'.
    I know the columns exist because I can open the table and see them. I have excluded some columns because I was getting an error saying it can't update values to a ZLS and that error went away when I just removed those columns from the statement.

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    if you're working with SQL Server syntax : use ' to indicate text strings like set endtime = '000'
    If you send endtime = "000" sql sees this as the column named 000, not as a string value

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That is the point, some are single quotes, others are double quotes?
    There does not appear to be a single number there?

    Is that normal for SQL Server?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    For SQL server the expression
    SET DepartmentWorkedIn = "00"
    means :set the column [DepartmentWorkedIn] equal to the column [00] column names can be indicated by using [column name] or "column name", hence the error message Invalid column name

    the expression in the debug window should read:
    Code:
    UPDATE dbo.JT_DailyTimeEntry SET EndTime = '0000', Overtime = 'N', NewWTStatus = 'COM', NewStatusComment = 'T/T COMPLETED'

    and so on

    If you want to run a passthrough query, you must provide the correct syntax for the databse server and access SQL differs from standard SQL syntax in many ways
    For instance the function for getting the current date is getdate() and not currentdate


    Last edited by NoellaG; 02-23-2023 at 10:48 AM. Reason: typo

  5. #5
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by NoellaG View Post
    if you're working with SQL Server syntax : use ' to indicate text strings like set endtime = '000'
    If you send endtime = "000" sql sees this as the column named 000, not as a string value
    This fixed it! I had half of them in double quotes and half of them in single. I was just being a bit blind because of the amount of values. I couldn't believe I'd missed that, thanks!
    p.s. sorry for the late reply. I thought I already replied.

    Quote Originally Posted by NoellaG View Post
    For SQL server the expression
    If you want to run a passthrough query, you must provide the correct syntax for the databse server and access SQL differs from standard SQL syntax in many ways
    For instance the function for getting the current date is getdate() and not currentdate
    Also just to note CurrentDate is a custom function I made to convert standard time format into the unique time that our SQL server uses.
    Code:
    'EX: 13.66447 ='13 = 1PM.
    '(0.66447x60) = 39.8682 so minutes is 39 or 1:39PM
    '(0.8682x60) = 52.092 so seconds is 52 or 1:39:52PM
    '(0.092x1000) = 92 so milliseconds is 92 most likely don't need anyways but it would be 1:39:52.92PM
    Couldn't find anything on this time format so I wrote a function to do this and another to reverse it.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Debug.Print your SQL string before trying to use it.
    That generally shows you your errors, and if not you can post the output back here for someone to look at?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Welshgasman View Post
    Debug.Print your SQL string before trying to use it.
    That generally shows you your errors, and if not you can post the output back here for someone to look at?
    Oh I did! It works fine! Just wish I had a name for this time format. I'll leave the post open for a few days incase someone knows the name of it.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Unix format perhaps?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What time format? You're referring to 0.66447?
    Date and time is stored as a floating point double. When you see dates and times (e.g. date value is 01/25/2023) you are seeing a format of the number (which is 44951.0 using that date).
    Last edited by Micron; 03-01-2023 at 12:07 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by Welshgasman View Post
    Unix format perhaps?
    I don't think so? It is just time with no date. I can't find any unix format that does that. It seems unix multiplies by 86?

    Quote Originally Posted by Micron View Post
    What time format? You're referring to 0.66447?
    Date and time is stored as a floating point double. When you see dates and times (e.g. date value is 01/25/2023) you are seeing a format of the number (which is 44951.0 using that date).
    No dates are involved just time. This is how the math is done to decode it into standard 12 hour time format (hh:mm:ss:ms)
    Code:
    'EX: 13.66447 ='13 = 1PM.
    '(0.66447x60) = 39.8682 so minutes is 39 or 1:39PM
    '(0.8682x60) = 52.092 so seconds is 52 or 1:39:52PM
    '(0.092x1000) = 92 so milliseconds is 92 most likely don't need anyways but it would be 1:39:52.92PM
    The dates are done in an entirely separate column so this is just time. the "13.66447" is how it is stored in the server. 13 equals 1PM in 24hours time and then you multiply the decimals by 60 and take the whole number for minutes, repeat for seconds then you have the milliseconds too.

    I just don't know what this method is called.

  11. #11
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Then saving time into a number (decimal or float) type?

    If you have to do calculation to get time structure then it is not stored in a date/time type. Calculation is converting from decimal hours to time structure. I suspect function returns a string, not an actual date/time.

    BTW, basically same calculation for converting decimal degrees to degrees:minutes:seconds.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Vita's Avatar
    Vita is offline Competent Performer
    Windows 10 Access 2002
    Join Date
    May 2022
    Location
    Massachusetts, USA
    Posts
    296
    Quote Originally Posted by June7 View Post
    Then saving time into a number (decimal or float) type?

    If you have to do calculation to get time structure then it is not stored in a date/time type. Calculation is converting from decimal hours to time structure. I suspect function returns a string, not an actual date/time.

    BTW, basically same calculation for converting decimal degrees to degrees:minutes:seconds.
    Yea basically. Here, this is the code. Hopefully it will help explain what I mean better. Maybe there are some ways to optimize it that I didn't think of either. (Not that it takes long but just like potentially there's a bad habit or some better way to do something I can keep in mind! Would also appreciate mentioning good habits I have done!)

    I realize in this one I can get rid of the optional FieldName.
    Code:
    'EX: 13.66447 =
    '13 = 1PM.
    '(0.66447x60) = 39.8682 so minutes is 39 or 1:39PM
    '(0.8682x60) = 52.092 so seconds is 52 or 1:39:52PM
    '(0.092x1000) = 92 so milliseconds is 92 most likely dont need anyways but it would be 1:39:52.92PM
    
    Public Function TimeConvert(Time As String, Optional FieldName As String)
    Dim Hours As String, Minutes As String, Seconds As String, MSeconds As String, TimePos As String, AMPM As String, i As Integer
    TimePos = "Hours"
    'Function to convert the time format of SQL to standard time format. Special thanks to Matt C. for figuring out the formula.
    Time = Format(Time, "00.00000")
            For i = 1 To Len(Time)
                Select Case TimePos
                    Case "Hours"
                        If Not Right(Left(Time, i), 1) = "." Then
                            Hours = Left(Time, i)
                        Else
                            Time = Mid(Time, Len(Hours) + 1, Len(Time) - Len(Hours))
                            Time = Format(Time * 60, "00.00000")
                            If Hours >= 12 Then
                                If Hours <> 12 Then
                                    Hours = Hours - 12
                                End If
                                AMPM = "PM"
                            Else
                                If Left(Hours, 1) = 0 Then
                                    Hours = Right(Left(Hours, 2), 1)
                                End If
                                AMPM = "AM"
                            End If
                            TimePos = "Minutes"
                            i = 0
                        End If
                    Case "Minutes"
                        If Not Right(Left(Time, i), 1) = "." Then
                            Minutes = Format(Left(Time, i), "00")
                        Else
                            Time = Mid(Time, Len(Minutes) + 1, Len(Time) - Len(Minutes))
                            Time = Format(Time * 60, "00.00000")
                            TimePos = "Seconds"
                            i = 0
                        End If
                    Case "Seconds"
                        If Not Right(Left(Time, i), 1) = "." Then
                            Seconds = Format(Left(Time, i), "00")
                        Else
                            Time = Mid(Time, Len(Seconds) + 1, Len(Time) - Len(Seconds))
                            Time = Time * 1000
                            TimePos = "MSeconds"
                            i = 0
                        End If
                    Case "MSeconds"
                        MSeconds = Time
                        i = 100
                    Case Else
                        MsgBox "Error in TimeConvert() Function (Select Else)"
                End Select
            Next
            
            TimeConvert = Hours & ":" & Minutes & " " & AMPM
            'Uncomment to paste full time into debug window
            'Debug.Print Nz(FieldName, "Time Full Format") & ": " & Hours & ":" & Minutes & ":" & Seconds & "." & MSeconds & " " & AMPM
    End Function
    Code:
    Public Function CurrentTime()
    Dim StrTime As String, chrString As String, CurrTime As String, MSeconds As String
    Dim i As Integer, ColonCount As Integer
    CurrTime = CStr(Time()) '"9:59:30"
    MSeconds = Right(Format(Timer, "0.000"), 4) '".084"
    'Function to convert standard time into the SQL format we use.
    For i = 1 To Len(CurrTime)
        chrString = Mid(CurrTime, i, 1)
        Select Case Asc(chrString)
            Case 48 To 58
                StrTime = StrTime + chrString
            Case Else
                i = Len(CurrTime)
                CurrTime = ""
        End Select
    Next
    
    
    StrTime = Format(StrTime, "hh:mm:ss")
    For i = 1 To Len(StrTime)
        If Mid(StrTime, i, 1) = ":" Then
            ColonCount = ColonCount + 1
        End If
    Next
    While ColonCount <> -1
        Select Case ColonCount
            Case 2
                CurrTime = Format(((Right(StrTime, 2) & MSeconds) / 60), ".00000")
                StrTime = Left(StrTime, Len(StrTime) - 3)
                ColonCount = ColonCount - 1
            Case 1
                CurrTime = Format(((Right(StrTime, 2) & CurrTime) / 60), ".00000")
                StrTime = Left(StrTime, Len(StrTime) - 3)
                ColonCount = ColonCount - 1
            Case 0
                CurrTime = StrTime & CurrTime
                If Left(CurrTime, 1) = 0 Then
                    CurrTime = Mid(CurrTime, 2, Len(CurrTime) - 1)
                End If
                ColonCount = ColonCount - 1
        End Select
    Wend
    CurrentTime = CurrTime
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Whats wrong with this
    By dmyoungsal in forum Access
    Replies: 10
    Last Post: 05-24-2018, 04:37 PM
  2. Can't figure out where I am going wrong
    By sknechte in forum Queries
    Replies: 3
    Last Post: 02-04-2018, 03:25 PM
  3. Replies: 15
    Last Post: 01-03-2018, 03:31 PM
  4. Whats wrong with my query
    By Kirtap in forum Queries
    Replies: 5
    Last Post: 10-01-2013, 10:31 AM
  5. Whats wrong with this code?
    By shabbaranks in forum Programming
    Replies: 2
    Last Post: 03-20-2012, 08:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums