Run-time error '3075'

[ozgenaga]

Hi guys,

I am trying to insert new data records to a table but I am getting a run-time error. I would appreciate any kind of help.

Here is my code:

Sub WorstQueryEver()

Dim db As Database
Dim rs1 As DAO.QueryDef
Dim rs2 As DAO.QueryDef
Dim strGivenAlarm As String
Dim rec1 As Recordset
Dim rec2 As Recordset
Dim TimeVar(1000) As Date
Dim WTGNo(1000) As String



Set db = CurrentDb()

strGivenAlarm = "SELECT PreviousAlarmsInSixHoursforAGiv.timeStampEv, PreviousAlarmsInSixHoursforAGiv.WTG, " & _
                "PreviousAlarmsInSixHoursforAGiv.AlCode, PreviousAlarmsInSixHoursforAGiv.AlarmNo " & _
                "FROM PreviousAlarmsInSixHoursforAGiv " & _
                "WHERE (PreviousAlarmsInSixHoursforAGiv.AlCode =799) ORDER BY PreviousAlarmsInSixHoursforAGiv.AlarmNo;"


i = 2
j = i - 1
                        
strCopyAlarmsInBetweenGivenTime = "INSERT INTO test22 SELECT DISTINCTROW PreviousAlarmsInSixHoursforAGiv.AlCode, " & _
                        "PreviousAlarmsInSixHoursforAGiv.alarm, PreviousAlarmsInSixHoursforAGiv.timeStampEv " & _
                        "FROM MostFreqPriorAlarms INNER JOIN PreviousAlarmsInSixHoursforAGiv ON " & _
                        "MostFreqPriorAlarms.alarm = PreviousAlarmsInSixHoursforAGiv.alarm " & _
                        "WHERE (((PreviousAlarmsInSixHoursforAGiv.timeStampEv) Between " & TimeVar(j) & _
                        " And " & TimeVar(i) & ") AND " & _
                        "(PreviousAlarmsInSixHoursforAGiv.WTG = " & WTGNo(i) & "));"

Set rec1 = db.OpenRecordset(strGivenAlarm)


Do While Not rec1.EOF

    TimeVar(i) = rec1.Fields(0)
    WTGNo(i) = rec1.Fields(1)
    
    If i > 2 Then
       
        CurrentDb.Execute (strCopyAlarmsInBetweenGivenTime)
        
    End If
    
    rec1.MoveNext
    i = i + 1
    j = i - 1
Loop

End Sub

And here is the error message:

Run-time error '3075':

Syntax error (missing operator) in query expression '(((PreviousAla rmsInSixHoursfo rAGiv.timeStamp Ev) Between 12:00:00 AM And 12:00:00 AM) AND (PreviousAlarms InSixHoursforAG iv.WTG = ))'.

[erict]

Off the top of my head I would say that the problem is in the .WTG = )) portion of the SQL. There does not appear to be a value in the right side of the test

[ozgenaga]

Thank you so much for your input,

But when I use the same array element "WTGNo(i)" in a message box, it shows the value I want, but I don't know why it is not recognized by the sql query.

On the other hand, when I removed the WTG part of the WHERE statement, I get a similar error:

Run-time error '3075':

Syntax error (missing operator) in query expression '(((PreviousAla rmsInSixHoursfo rAGiv.timeStamp Ev) Between 12:00:00 AM And 12:00:00 AM))'.

Does any one have any idea about this? I would appreciate any kind of brain storming as well.

[FishVal]

You need to put date constant into # delimiters.
As well as solve that issue with empty WTG array element.

Regards,
Fish

[ozgenaga]

Hi FishVal,

Should I use the delimiter in the variable definition part or in my sql query (or both)?

Thanks

You need to put date constant into # delimiters.
As well as solve that issue with empty WTG array element.

Regards,
Fish

[FishVal]

The delimiters should appear in SQL expression.

[ozgenaga]

I think I got it!! Thank you so much.

One quick question though:

As you can see my Insert sql in a loop and it is asking the confirmation question everytime. Is there a way to avoid this "you are about to paste # rows into a new table" (if i use insert statement) or "the existing table test22 will be deleted before you run the query" (if I use select distincrow .... into.... statement) everytime ?

The delimiters should appear in SQL expression.

[FishVal]

I think I got it!! Thank you so much.

You are quite welcome.

One quick question though:

As you can see my Insert sql in a loop and it is asking the confirmation question everytime. Is there a way to avoid this "you are about to paste # rows into a new table" (if i use insert statement) or "the existing table test22 will be deleted before you run the query" (if I use select distincrow .... into.... statement) everytime ?

:)
A quick answer:
Use DoCmd.SetWarnin gs method to get rid off those annoying alerts.
Though make sure to turn them on after SQL commands has been executed,
since Access application doesn't return to previous warnings setting when code execution stops.

Regards,
Fish.

[ozgenaga]

Wow, you are great!!

But don't spoil me or I will keep asking questions :))

Another thing:

I don't know why the value of my date variable appear shorter in sql command.

When I use msgbox command to check if my date values are OK, I see the result in 'dd/mm/yyyy hh:mm:ss AM' format which is perfect. But when in sql query (I see this in the error warning window by adding an extra parenthesis ) I only see the 'hh:mm:ss AM' part and also the value is wrong. For example, I am supposed to see 10:50:55 AM, but I see 12:00:00AM everytime.

Do you have any idea about it?

You are quite welcome.



:)
A quick answer:
Use DoCmd.SetWarnin gs method to get rid off those annoying alerts.
Though make sure to turn them on after SQL commands has been executed,
since Access application doesn't return to previous warnings setting when code execution stops.

Regards,
Fish.

[FishVal]

SQL syntax expects date constants to be in m/d/y format.
(Not sure about time portion - seems h:m:s AM/PM is ok, though, maybe 24-hour format required.)
When date is not valid in terms of this format Access silently tries to "correct" it appropriately using different methods at that ;). Results are somewhat unpredictable.

I recommend you to design a simple function to convert date value to a string which could be merged into SQL expression.
Something like the following:
[code=vb]
Public Function SQLDate(dte As Date) As String
'add delimiters and format as m/d/y with 24-hour time portion
SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss") & "#"
End Function
[/code]

Regards,
Fish

[ozgenaga]

Thanks man!

Now it is showing the whole string, but there is something really weird:

The original dates are " 9/16/2008 10:50:55 AM" and " 11/7/2008 5:42:47 AM" but the sql error shows " 12/30/1899 12:00:00 AM" for both. I mean, what is it to do with 1899? I don't have such a value in my table. I am really close to get crazy... Do you think this is a bug or, I don't know, a logic error?

SQL syntax expects date constants to be in m/d/y format.
(Not sure about time portion - seems h:m:s AM/PM is ok, though, maybe 24-hour format required.)
When date is not valid in terms of this format Access silently tries to "correct" it appropriately using different methods at that ;). Results are somewhat unpredictable.

I recommend you to design a simple function to convert date value to a string which could be merged into SQL expression.
Something like the following:
[code=vb]
Public Function SQLDate(dte As Date) As String
'add delimiters and format as m/d/y with 24-hour time portion
SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss") & "#"
End Function
[/code]

Regards,
Fish

[FishVal]

12/30/1899 12:00:00 AM is "zero date".
Could it be so that variable you format as date actually =0?

Before trying execute some string as SQL expression get its value using Debug.Print command to see what it actually contains.

Try to further localize actual error source using VBA debugging facilities.

[ozgenaga]

It is really not zero. I have debugged it thousand different ways, it's showing me the exact value in the table. Can there be any other reason (table format, sql query, etc.) which is causing this?

12/30/1899 12:00:00 AM is "zero date".
Could it be so that variable you format as date actually =0?

Before trying execute some string as SQL expression get its value using Debug.Print command to see what it actually contains.

Try to further localize actual error source using VBA debugging facilities.

[FishVal]

Please, post your code as it looks now.

[ozgenaga]

Option Compare Database

Sub WorstQueryEver()

Dim db As Database
Dim rs1 As DAO.QueryDef
Dim rs2 As DAO.QueryDef
Dim strGivenAlarm As String
Dim rec1 As Recordset
Dim rec2 As Recordset
Dim TimeVar(1000) As Date
Dim TimeVarLeft(1000) As Date
Dim TimeVarRight(1000) As String
Dim WTGNo(1000) As String
Dim WhereIsSpace As Integer
Dim LenR As Integer



Set db = CurrentDb()

strGivenAlarm = "SELECT PreviousAlarmsInSixHoursforAGiv.timeStampEv, PreviousAlarmsInSixHoursforAGiv.WTG, " & _
                "PreviousAlarmsInSixHoursforAGiv.AlCode, PreviousAlarmsInSixHoursforAGiv.AlarmNo " & _
                "FROM PreviousAlarmsInSixHoursforAGiv " & _
                "WHERE (PreviousAlarmsInSixHoursforAGiv.AlCode =799) ORDER BY PreviousAlarmsInSixHoursforAGiv.AlarmNo;"


strMostFrequentAlarms = "SELECT TOP 20 PreviousAlarmsInSixHoursforAGiv.AlCode" & _
                        "FROM PreviousAlarmsInSixHoursforAGiv GROUP BY PreviousAlarmsInSixHoursforAGiv.alarm, " & _
                        "PreviousAlarmsInSixHoursforAGiv.AlCode ORDER BY Count(*) DESC;"
i = 2
j = i - 1
                        
strCopyAlarmsInBetweenGivenTime = "SELECT DISTINCTROW PreviousAlarmsInSixHoursforAGiv.AlCode, " & _
                        "PreviousAlarmsInSixHoursforAGiv.alarm, PreviousAlarmsInSixHoursforAGiv.timeStampEv " & _
                        "INTO test22 FROM MostFreqPriorAlarms " & _
                        "INNER JOIN PreviousAlarmsInSixHoursforAGiv ON " & _
                        "MostFreqPriorAlarms.alarm = PreviousAlarmsInSixHoursforAGiv.alarm " & _
                        "WHERE (PreviousAlarmsInSixHoursforAGiv.timeStampEv Between " & SQLDate(TimeVar(j)) & _
                        " And " & SQLDate(TimeVar(i)) & ") AND " & _
                        "(PreviousAlarmsInSixHoursforAGiv.WTG = '" & WTGNo(i) & "');"
                        

Set rec1 = db.OpenRecordset(strGivenAlarm)


Do While Not rec1.EOF

    TimeVar(i) = rec1.Fields(0)
    
    WTGNo(i) = rec1.Fields(1)
    
      
    If i > 2 Then
        Debug.Print SQLDate(TimeVar(i))
        Debug.Print SQLDate(TimeVar(j))
    
        MsgBox ("a= " & TimeVar(j) & " and b= " & TimeVar(i) & " and WTG = " & WTGNo(i) & " table is " & WTGNo(i) & "-" & i)
        DoCmd.RunSQL strCopyAlarmsInBetweenGivenTime
    
    End If
    
    rec1.MoveNext
    i = i + 1
    j = i - 1
Loop

i = 2
j = 1


End Sub



 Public Function SQLDate(dte As Date) As String
 
 'add delimiters and format as m/d/y with 24-hour time portion
 SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss AM/PM") & "#"
 
 End Function

Please, post your code as it looks now.