By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Run-time error '3075'

P: 14
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:

Expand|Select|Wrap|Line Numbers
  1. Sub WorstQueryEver()
  2.  
  3. Dim db As Database
  4. Dim rs1 As DAO.QueryDef
  5. Dim rs2 As DAO.QueryDef
  6. Dim strGivenAlarm As String
  7. Dim rec1 As Recordset
  8. Dim rec2 As Recordset
  9. Dim TimeVar(1000) As Date
  10. Dim WTGNo(1000) As String
  11.  
  12.  
  13.  
  14. Set db = CurrentDb()
  15.  
  16. strGivenAlarm = "SELECT PreviousAlarmsInSixHoursforAGiv.timeStampEv, PreviousAlarmsInSixHoursforAGiv.WTG, " & _
  17.                 "PreviousAlarmsInSixHoursforAGiv.AlCode, PreviousAlarmsInSixHoursforAGiv.AlarmNo " & _
  18.                 "FROM PreviousAlarmsInSixHoursforAGiv " & _
  19.                 "WHERE (PreviousAlarmsInSixHoursforAGiv.AlCode =799) ORDER BY PreviousAlarmsInSixHoursforAGiv.AlarmNo;"
  20.  
  21.  
  22. i = 2
  23. j = i - 1
  24.  
  25. strCopyAlarmsInBetweenGivenTime = "INSERT INTO test22 SELECT DISTINCTROW PreviousAlarmsInSixHoursforAGiv.AlCode, " & _
  26.                         "PreviousAlarmsInSixHoursforAGiv.alarm, PreviousAlarmsInSixHoursforAGiv.timeStampEv " & _
  27.                         "FROM MostFreqPriorAlarms INNER JOIN PreviousAlarmsInSixHoursforAGiv ON " & _
  28.                         "MostFreqPriorAlarms.alarm = PreviousAlarmsInSixHoursforAGiv.alarm " & _
  29.                         "WHERE (((PreviousAlarmsInSixHoursforAGiv.timeStampEv) Between " & TimeVar(j) & _
  30.                         " And " & TimeVar(i) & ") AND " & _
  31.                         "(PreviousAlarmsInSixHoursforAGiv.WTG = " & WTGNo(i) & "));"
  32.  
  33. Set rec1 = db.OpenRecordset(strGivenAlarm)
  34.  
  35.  
  36. Do While Not rec1.EOF
  37.  
  38.     TimeVar(i) = rec1.Fields(0)
  39.     WTGNo(i) = rec1.Fields(1)
  40.  
  41.     If i > 2 Then
  42.  
  43.         CurrentDb.Execute (strCopyAlarmsInBetweenGivenTime)
  44.  
  45.     End If
  46.  
  47.     rec1.MoveNext
  48.     i = i + 1
  49.     j = i - 1
  50. Loop
  51.  
  52. End Sub
  53.  
And here is the error message:

Run-time error '3075':

Syntax error (missing operator) in query expression '(((PreviousAlarmsInSixHoursforAGiv.timeStampEv) Between 12:00:00 AM And 12:00:00 AM) AND (PreviousAlarmsInSixHoursforAGiv.WTG = ))'.
Nov 18 '08 #1
Share this Question
Share on Google+
22 Replies


P: 4
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
Nov 19 '08 #2

P: 14
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 '(((PreviousAlarmsInSixHoursforAGiv.timeStampEv) 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.
Nov 19 '08 #3

FishVal
Expert 2.5K+
P: 2,653
You need to put date constant into # delimiters.
As well as solve that issue with empty WTG array element.

Regards,
Fish
Nov 19 '08 #4

P: 14
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
Nov 19 '08 #5

FishVal
Expert 2.5K+
P: 2,653
The delimiters should appear in SQL expression.
Nov 19 '08 #6

P: 14
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.
Nov 19 '08 #7

FishVal
Expert 2.5K+
P: 2,653
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.SetWarnings 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.
Nov 19 '08 #8

P: 14
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.SetWarnings 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.
Nov 19 '08 #9

FishVal
Expert 2.5K+
P: 2,653
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function SQLDate(dte As Date) As String
  2.     'add delimiters and format as m/d/y with 24-hour time portion
  3.     SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss") & "#"
  4. End Function
  5.  
Regards,
Fish
Nov 19 '08 #10

P: 14
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function SQLDate(dte As Date) As String
  2.     'add delimiters and format as m/d/y with 24-hour time portion
  3.     SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss") & "#"
  4. End Function
  5.  
Regards,
Fish
Nov 20 '08 #11

FishVal
Expert 2.5K+
P: 2,653
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.
Nov 20 '08 #12

P: 14
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.
Nov 20 '08 #13

FishVal
Expert 2.5K+
P: 2,653
Please, post your code as it looks now.
Nov 20 '08 #14

P: 14
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Sub WorstQueryEver()
  4.  
  5. Dim db As Database
  6. Dim rs1 As DAO.QueryDef
  7. Dim rs2 As DAO.QueryDef
  8. Dim strGivenAlarm As String
  9. Dim rec1 As Recordset
  10. Dim rec2 As Recordset
  11. Dim TimeVar(1000) As Date
  12. Dim TimeVarLeft(1000) As Date
  13. Dim TimeVarRight(1000) As String
  14. Dim WTGNo(1000) As String
  15. Dim WhereIsSpace As Integer
  16. Dim LenR As Integer
  17.  
  18.  
  19.  
  20. Set db = CurrentDb()
  21.  
  22. strGivenAlarm = "SELECT PreviousAlarmsInSixHoursforAGiv.timeStampEv, PreviousAlarmsInSixHoursforAGiv.WTG, " & _
  23.                 "PreviousAlarmsInSixHoursforAGiv.AlCode, PreviousAlarmsInSixHoursforAGiv.AlarmNo " & _
  24.                 "FROM PreviousAlarmsInSixHoursforAGiv " & _
  25.                 "WHERE (PreviousAlarmsInSixHoursforAGiv.AlCode =799) ORDER BY PreviousAlarmsInSixHoursforAGiv.AlarmNo;"
  26.  
  27.  
  28. strMostFrequentAlarms = "SELECT TOP 20 PreviousAlarmsInSixHoursforAGiv.AlCode" & _
  29.                         "FROM PreviousAlarmsInSixHoursforAGiv GROUP BY PreviousAlarmsInSixHoursforAGiv.alarm, " & _
  30.                         "PreviousAlarmsInSixHoursforAGiv.AlCode ORDER BY Count(*) DESC;"
  31. i = 2
  32. j = i - 1
  33.  
  34. strCopyAlarmsInBetweenGivenTime = "SELECT DISTINCTROW PreviousAlarmsInSixHoursforAGiv.AlCode, " & _
  35.                         "PreviousAlarmsInSixHoursforAGiv.alarm, PreviousAlarmsInSixHoursforAGiv.timeStampEv " & _
  36.                         "INTO test22 FROM MostFreqPriorAlarms " & _
  37.                         "INNER JOIN PreviousAlarmsInSixHoursforAGiv ON " & _
  38.                         "MostFreqPriorAlarms.alarm = PreviousAlarmsInSixHoursforAGiv.alarm " & _
  39.                         "WHERE (PreviousAlarmsInSixHoursforAGiv.timeStampEv Between " & SQLDate(TimeVar(j)) & _
  40.                         " And " & SQLDate(TimeVar(i)) & ") AND " & _
  41.                         "(PreviousAlarmsInSixHoursforAGiv.WTG = '" & WTGNo(i) & "');"
  42.  
  43.  
  44. Set rec1 = db.OpenRecordset(strGivenAlarm)
  45.  
  46.  
  47. Do While Not rec1.EOF
  48.  
  49.     TimeVar(i) = rec1.Fields(0)
  50.  
  51.     WTGNo(i) = rec1.Fields(1)
  52.  
  53.  
  54.     If i > 2 Then
  55.         Debug.Print SQLDate(TimeVar(i))
  56.         Debug.Print SQLDate(TimeVar(j))
  57.  
  58.         MsgBox ("a= " & TimeVar(j) & " and b= " & TimeVar(i) & " and WTG = " & WTGNo(i) & " table is " & WTGNo(i) & "-" & i)
  59.         DoCmd.RunSQL strCopyAlarmsInBetweenGivenTime
  60.  
  61.     End If
  62.  
  63.     rec1.MoveNext
  64.     i = i + 1
  65.     j = i - 1
  66. Loop
  67.  
  68. i = 2
  69. j = 1
  70.  
  71.  
  72. End Sub
  73.  
  74.  
  75.  
  76.  Public Function SQLDate(dte As Date) As String
  77.  
  78.  'add delimiters and format as m/d/y with 24-hour time portion
  79.  SQLDate = "#" & Format(dte, "mm/dd/yyyy hh:mm:ss AM/PM") & "#"
  80.  
  81.  End Function



Please, post your code as it looks now.
Nov 20 '08 #15

P: 14
I tried to pass these following variables instead of SQLDate(TimeVar(i)) and SQLDate(TimeVar(j)) into my sql query:

TestVar1 = "#9/16/2008 10:50:55 AM#"
TestVar2 = "#11/7/2008 5:42:47 AM#"

And they didn't show up in the error window, so I believe there is something wrong with the way I am passing my variables.God, this is frustrating :)
Nov 20 '08 #16

FishVal
Expert 2.5K+
P: 2,653
I tried to pass these following variables instead of SQLDate(TimeVar(i)) and SQLDate(TimeVar(j)) into my sql query:

TestVar1 = "#9/16/2008 10:50:55 AM#"
TestVar2 = "#11/7/2008 5:42:47 AM#"

And they didn't show up in the error window, so I believe there is something wrong with the way I am passing my variables.God, this is frustrating :)
Well. :)

The problem is that you don't merge dates into the SQL expression at all.
Only once - before the cycle when both variables are zeroes, no wonder you get 12/30/1899 12:00:00 AM in you query.

Kind regards,
Fish
Nov 20 '08 #17

P: 14
No, I do merge (or I didn't understand what you said).

If you check my code, you will see the i am debugging the variables right before I call the sql query.

With the last message, I tried to say; even if I use a string variable that is constant, it doesn't show up in the sql query.

If you think that I didn't totally understand you, can you help me on where in the code I should make changes?

Thank you

Well. :)

The problem is that you don't merge dates into the SQL expression at all.
Only once - before the cycle when both variables are zeroes, no wonder you get 12/30/1899 12:00:00 AM in you query.

Kind regards,
Fish
Nov 20 '08 #18

P: 14
Here is the error shows up when I use TestVar1 and TestVar2:

Run-time error '3075':

Syntax error (missing operator) in query expression
'(PreviousAlarmsInSixHoursforAGiv.timeStampEv Between And ) AND
(PreviousAlarmsInSixHoursforaGiv.WTG = ''))'.


And the error shows up when I use SQLDate(TimeVar(j)) and SQLDate(TimeVar(i)):

Run-time error '3075':

Extra ) in query expression
'(PreviousAlarmsInSixHoursforAGiv.timeStampEv Between
#12/30/1899 12:00:00 AM# And #12/30/1899 12:00:00 AM# ) AND
(PreviousAlarmsInSixHoursforaGiv.WTG = ''))'.


I hope this helps for better understanding. Sorry for being a pain in the butt

P.S: TestVar1 = SQLDate(TimeVar(j)) when j = 2
TestVar2 = SQLDate(TimeVar(i)) when i = 3
Nov 20 '08 #19

FishVal
Expert 2.5K+
P: 2,653
No, I do merge (or I didn't understand what you said).

If you check my code, you will see the i am debugging the variables right before I call the sql query....
That seemed obvious, ... but ... ok
In few words

Assigning new values to TestVar1 and TestVar2 variables will not change strCopyAlarmsInBetweenGivenTime variable.

strCopyAlarmsInBetweenGivenTime variable gets value (with "zero dates") one time before the loop and never after.

Debugging variables is very nice, but useful only if their values have an influence on code execution. In your code values of TestVar1 and TestVar2 variables are used for viewing via MsgBox only.

Kind regards,
Fish
Nov 21 '08 #20

P: 14
Thanks, Fish! But I figured it out!

The problem was my sql string was out of the loop. I used program in asp and defining sql string out of the loop and calling it later was OK within ASP but looks like it works little bit different with VBA.

Thank you one more time, man!!

That seemed obvious, ... but ... ok
In few words

Assigning new values to TestVar1 and TestVar2 variables will not change strCopyAlarmsInBetweenGivenTime variable.

strCopyAlarmsInBetweenGivenTime variable gets value (with "zero dates") one time before the loop and never after.

Debugging variables is very nice, but useful only if their values have an influence on code execution. In your code values of TestVar1 and TestVar2 variables are used for viewing via MsgBox only.

Kind regards,
Fish
Nov 21 '08 #21

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish.

P.S. Will you change after that name of the sub? :D
Nov 21 '08 #22

P: 14
Yeap, I already called it "Code de la Fish" :)


You are welcome.

Best regards,
Fish.

P.S. Will you change after that name of the sub? :D
Nov 21 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.