Missing Operator in SQL string based on form values 2

[kpal29]

I keep getting a runtime error saying 'Syntax Error missing operator in query expression' but I can't see where I am missing something? I need to get two values from a subform that is open at runtime. This sql works fine in the query panel.

UndoImport = "SELECT PRMFinancials.FileCode, PRMFinancials.[Client Code], PRMFinancials.ImportDate, " & _
"PRMFinancials.[Charge Code], PRMFinancials.Value, PRMFinancials.[ETA Month], " & _
"PRMFinancials.[ETD Month], PRMFinancials.Income FROM PRMFinancials " & _
"WHERE (((PRMFinancials.FileCode)= " & FileCode & ") " & _
"AND ((PRMFinancials.ImportDate)= " & ImportDate & "));"
[end code]

 

[traingamer]

Most likely culprit is

"WHERE (((PRMFinancials.FileCode)= " & FileCode & ") " & _
"AND ((PRMFinancials.ImportDate)= [COLOR=red]#[/color]" & ImportDate & "[COLOR=red]#[/color]));"

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[kpal29]

I added the pound signs and getting the same error. The date field includes date, time and AM/PM at the end. Could this be messing me up?

Complete code below:

Sub UndoImportData()
Dim db As DAO.Database
Set db = CurrentDb

Dim FileCode As String
Dim ImportDate As String

FileCode = [Forms]![FileImport]![ImportHistory].[Form]![File Imported Type]
ImportDate = [Forms]![FileImport]![ImportHistory].[Form]![File Imported Date]
Dim UndoImport As String

UndoImport = "SELECT PRMFinancials.FileCode, PRMFinancials.[Client Code], PRMFinancials.ImportDate, " & _
"PRMFinancials.[Charge Code], PRMFinancials.Value, PRMFinancials.[ETA Month], " & _
"PRMFinancials.[ETD Month], PRMFinancials.Income FROM PRMFinancials " & _
"WHERE (((PRMFinancials.FileCode)= " & FileCode & ") " & _
"AND ((PRMFinancials.ImportDate)= #" & ImportDate & "#));"

Dim UndoRecords As DAO.Recordset 'acts like a table or query object
Set UndoRecords = db.OpenRecordset(UndoImport) 'Opens the table
rstPRMFinancials.MoveFirst
'______________________________________________________________________________
    
Do While Not UndoRecords.EOF
UndoRecords.Edit
    UndoRecords.Delete
    UndoRecords.MoveNext
Loop
'________________________________________________________________________________

UndoRecords.Close
End Sub
[end code]

 

[traingamer]

If FileCode is a string, you need to quote it. (If a number, should be ok).

"WHERE (((PRMFinancials.FileCode)= [COLOR=red]'[/color]" & FileCode & "[COLOR=red]'[/color]) " & _
"AND ((PRMFinancials.ImportDate)= #" & ImportDate & "#));"

 

[kpal29]

Now that I have deleted the records from the data table. I need to delete the ImportRecord from the history table.

The history table is updated from a form open at run time with import information such as file type and import date. If the user runs the UndoImport procedure, I also need to delete the Import History record from the PRMImportFileHistory record.

Now I get error "Too Few parameters expected 2. The datasheet subform is open at runtime and the record with the parameters is selected.

The code I have is as follow:

Dim HistoryData As String
FileCode2 = [Forms]![FileImport]![ImportHistory].[Form]![File Imported Type]
ImportDate2 = [Forms]![FileImport]![ImportHistory].[Form]![File Imported Date]

HistoryData = "Select PRMImportFileHistory.Directory, PRMImportFileHistory.[File Imported Type], PRMImportFileHistory.[File Imported Date], PRMImportFileHistory.[File ETD Month], PRMImportFileHistory.[File ETD Year] from PRMImportFileHistory where " & _
"(((PRMFinancials.FileCode)= '" & FileCode2 & "') " & _
"AND ((PRMFinancials.ImportDate)= #" & ImportDate2 & "#));"

Dim HistoryRecords As DAO.Recordset 'acts like a table or query object
Set HistoryRecords = db.OpenRecordset(HistoryData) 'Opens the table

HistoryRecords.MoveFirst
    
Do While Not UndoRecords.EOF
HistoryRecords.Edit
    HistoryRecords.Delete
    HistoryRecords.MoveNext
Loop

HistoryRecords.Close

MsgBox "The Data Import with File Type = " & FileCode & Chr(10) & _
"and Import Date = " & ImportDate & " has been deleted.", vbInformation

Forms!FileImport!ImportHistory.Form.Requery
End Sub
[code]

 

[traingamer]

Is this really what you meant?

HistoryRecords.MoveFirst
    
Do While Not [b]UndoRecords[/b].EOF
HistoryRecords.Edit

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[kpal29]

No, thanks for catching that. I meant

Do While Not HistoryRecords.EOF
HistoryRecords.Edit

I fixed in my code and still getting error 'Too Few Parameters'

 

[PHV]

Your query has a wrong WHERE clause, ie PRMFinancials instead of PRMImportFileHistory

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Good catch.

 

[kpal29]

Thanks to both of you!! It is working great now.
This website has the best programmers anywhere!!