Not an SQL Statement error? 1

[DreamerZ]

Please tell me what I'm doing wrong here. I'm getting the RunSQL action requires an SQL statement. I thought this was. What am I missing?

Dim strSQL As String

strSQL = "SELECT OfficialsAvailability.OfficialID, OfficialsAvailability.AvailabilityDate" & _
" FROM OfficialsAvailability" & _
" WHERE (((OfficialsAvailability.OfficialID)=[Forms]![RosterofOfficials]![ID])" & _
" AND ((OfficialsAvailability.AvailabilityDate)=[Forms]![RosterofOfficials]![AvailabilityDates]));"

DoCmd.RunSQL strSQL


Thx.

DreamerZ

 

[jebry]

Hi!

Do it like this:

Dim strSQL As String

strSQL = "SELECT OfficialsAvailability.OfficialID, OfficialsAvailability.AvailabilityDate" & _
" FROM OfficialsAvailability" & _
" WHERE OfficialsAvailability.OfficialID=" & [Forms]![RosterofOfficials]![ID] & _
" AND OfficialsAvailability.AvailabilityDate= #" & [Forms]![RosterofOfficials]![AvailabilityDates] & "#"

DoCmd.RunSQL strSQL

This assumes that the OfficialID is a number. If it is text then use:

" WHERE OfficialsAvailability.OfficialID= '" & [Forms]![RosterofOfficials]![ID] & "'" &_

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Junior1544]

first, I'd like to ask some thing... this statement, what is it you expect it to return??

To me it looks like it's a standard select statement that can't be run using the docmd.runsql because that is for action queries, and this looks like a select query...

Also, you have a few things there which are impimented wrong to use the sql in code... those i will correct here:

strSQL = "SELECT OfficialsAvailability.OfficialID, OfficialsAvailability.AvailabilityDate" & _
" FROM OfficialsAvailability" & _
" WHERE (((OfficialsAvailability.OfficialID)=" & [Forms]![RosterofOfficials]![ID] & &quot" & _
" AND ((OfficialsAvailability.AvailabilityDate)=" & [Forms]![RosterofOfficials]![AvailabilityDates] & &quot);"

See, the references to the forms/fields need to be out of the "'s... so that they return a string... right now it was looking for the reference to the field as the string to search for...

Meaning that is was searching for
"[Forms]![RosterofOfficials]![AvailabilityDates]"

hope this helps you at least a bit...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[DreamerZ]

Okay, well, then I think this is the problem. While the SQL wasn't technically correct, it is now and still doesn't work.

What I am trying to do is query a table with IDs, Names, and Dates. The names are unnecessary. They are just in the table for easy reference.

In a normal Select Query, with the criteria set as it is show in the SQL, it works fine. What I want to do however is check the table to see if the selection returns with a positive response, i.e. the selection was found. If it was found, I will delete it from the table. If it's not found, I will add it to the table.

Essentially, the form shows an official and a list box full of dates. After updating the list box (selecting a date, either one that was previously selected and now removing it, or selecting a new one), the table is searched and the action (add or delete the entry) is performed.

That help in what I want to do?

Thx.

DreamerZ

 

[Raptor136]

DreamerZ -- I found out today that SELECT queries don't work in docmd.runsql So I recommend you build a Delete query in the design grid. AS long as you don't hit the red exclamation point you can view the data to be deleted. Once you like the results, go to SQL view and then copy the code. After that, paste the code into the event procedure using the docmd.runsql and I think you might get what you are looking for. HTH -- Rap

 

[DreamerZ]

Okay, but the problem with a delete query is I don't want to run it unless it comes back true.

Essentially, this is the end code I want:

If (query) = true
Delete Selection
Else
Add Selection to Table
End if

So if the query is a delete query and comes back true, then that works, but if it's false, how do I then run the if?

Is there another way in the query to set the results to a variable in the query itself and not in the RunSQL?

 

[DreamerZ]

Bump.

Sorry, I still need help with this.

Thx.

 

[WayneRyan]

Hi,

The following code should do what you want.
I typed it in Notepad, so I'm sorry if there are any errors.

HTH
Wayne



Dim dbs as Database
Dim strSQL As String
Dim rst As Recordset

set dbs = CurrentDb

'
' Construct query to check table for presence of Form values ...
'
strSQL = "SELECT OfficialsAvailability.OfficialID, " & _
" OfficialsAvailability.AvailabilityDate " & _
"FROM OfficialsAvailability" & _
"WHERE (((OfficialsAvailability.OfficialID)=[Forms]![RosterofOfficials]![ID]) and " & _
" ((OfficialsAvailability.AvailabilityDate)=[Forms]![RosterofOfficials]![AvailabilityDates]));"

set rst = dbs.OpenRecordset(strSQL)

'
' If (end-of-file and beginning-of-file are true then insert; else delete ...
'
If rst.EOF and RST.BOF Then
strSQL = "Insert into OfficialsAvailability (OfficialID, AvailabilityDate) " & _
"Values(" & [Forms]![RosterofOfficials]![ID] & ", #" & _
[Forms]![RosterofOfficials]![AvailabilityDates] & "#);"
dbs.execute(strSQL)
Else
strSQL = "Delete from OfficialsAvailability " & _
"Where OfficialID = " & [Forms]![RosterofOfficials]![ID] & " And " & _
" AvailabilityDates = #" & [Forms]![RosterofOfficials]![AvailabilityDates] & "#);"
dbs.execute(strSQL)
End If

set rst = Nothing
Set dbs = Nothing

Exit Sub

'
' Oh no! They tortured the above text, Sorry
' Maybe paste it into an editor

 

[DreamerZ]

Your code worked fine, but I'm trying to add the text name to the SQL. Under the strSQL, I've added the following lines:

" AND ((OfficialsAvailability.OfficialName)=" & _
[Forms]![RosterofOfficials]![First] & &quot" & _

I'm getting a syntax error (missing operator) with those 2 lines in. Without them, they work fine. Thoughts? The full code is below.


Dim dbs As Database
Dim strSQL As String
Dim rst As Recordset

Set dbs = CurrentDb

'
' Construct query to check table for presence of Form values ...

strSQL = "SELECT OfficialsAvailability.OfficialID, OfficialsAvailability.OfficialName, OfficialsAvailability.AvailabilityDate" & _
" FROM OfficialsAvailability" & _
" WHERE (((OfficialsAvailability.OfficialID)=" & _
[Forms]![RosterofOfficials]![ID] & &quot" & _
" AND ((OfficialsAvailability.OfficialName)=" & _
[Forms]![RosterofOfficials]![First] & &quot" & _
" AND ((OfficialsAvailability.AvailabilityDate)=" & _
[Forms]![RosterofOfficials]![AvailabilityDates] & &quot);"

Set rst = dbs.OpenRecordset(strSQL)

'
' If (end-of-file and beginning-of-file are true then insert; else delete ...
'
If rst.EOF And rst.BOF Then
strSQL = "Insert into OfficialsAvailability (OfficialID, OfficialName, AvailabilityDate) " & _
"Values(" & [Forms]![RosterofOfficials]![ID] & ", #" & _
[Forms]![RosterofOfficials]![First] & ", #" & _
[Forms]![RosterofOfficials]![AvailabilityDates] & "#);"
dbs.Execute (strSQL)
Else
strSQL = "Delete from OfficialsAvailability " & _
"Where OfficialID = " & [Forms]![RosterofOfficials]![ID] & " And " & _
" AvailabilityDates = #" & [Forms]![RosterofOfficials]![AvailabilityDates] & "#);"
dbs.Execute (strSQL)
End If

Set rst = Nothing
Set dbs = Nothing

 

[DreamerZ]

Okay, skip the above post. The SQL statements work fine. I fixed everything.

The If Statement doesn't work. Even on a value that is old to the Officials Availability table, the If statement is returning true so it's adding duplicates rather than deleting the record.

Essentially, a new date in the combo box on the form is selected. It's added to the table. That works.

An old date in the combo box on the form is selected. It's deleted from the table. That doesn't work. The If is always returning true.

 

[WayneRyan]

To keep the text from wrapping I removed the
"OfficialsAvailability." since it would only
be required in a multi-table query. Also
changed "[RosterofOfficials]" to [RO].

Oh well, it still wraps ...


strSQL = "SELECT OfficialID, OfficialName, AvailabilityDate " & _
"FROM OfficialsAvailability " & _
"WHERE OfficialID = " & [Forms]![RO]![ID] & " And " & _
" OfficialName = '" & [Forms]![RO]![First] & "' and " & _
" AvailabilityDate = #" & [Forms]![RO]![AvailabilityDates] & "#;"

The end result of this statement is that strSQL has something correct to
hand to the SQL parser:

SELECT OfficialID, OfficialName, AvailabilityDate
FROM OfficialsAvailability
WHERE OfficialID = 117 And
OfficialName = 'Joe' and
AvailabilityDate = #9/10/02#;

The "117" needs no punctuation as it is a number.
The "Joe" needs the single quotes as it is a string.
The "9/10/02" needs the # as it is a date.

This should make the query return the proper results.

Good luck.

 

[DreamerZ]

THANK YOU! THANK YOU! THANK YOU!

The final SQL did work. I knew about the # signs for the date, but couldn't get the syntax correct. Single quotes for the string...doh!

Now in the IF statement, I think I'll just run a couple of queries: append and delete to do the changes to the table.

Thanks again! You earned your star on that one!

DreamerZ