Check for records before inserting variables using SQL Statement

[wideturn]

Hello, I am trying to check to see if records exists before inserting them into a table with a sql statement. I am using variables retrieved from a form and I can insert new records fine but when I check to see if the record exist I get a runtime error 3129 Invalid SQL Statement Expected Delete, Insert, etc... Any help would be greatly appreciated. This is the first time I have been at a complete loss.

Here is my code (Resulting SQL String below):

 
Private Sub Command9_Click()
Dim AddDays As Date
Dim sqlstring As String
 
 
DoCmd.SetWarnings False
 
AddDays = Me.txtBeginDate
 
For AddDays = Me.txtBeginDate To Me.txtEndDate
 
sqlstring = "if NOT EXISTS (SELECT ExpenseDate, ExpenseReportID " & _
"FROM tblExpenseDetail " & _
"WHERE ExpenseDate = " & "#" & AddDays & "#" & " AND ExpenseReportID = " & Me.txtExpenseReportID & ") " & _
"Insert Into tblExpenseDetail (ExpenseDate, ExpenseReportID, ExpenseHotel) " & _
"Values (#" & AddDays & "#, " & ExpenseReportID & ", " & ExpenseHotel & "); "
 
MsgBox sqlstring
 
DoCmd.RunSQL sqlstring
 
 
AddDays = DateAdd("d", 0, AddDays)
 
Next AddDays
 
DoCmd.SetWarnings True
 
End Sub
 
 
 

Resulting in:

if NOT EXISTS (SELECT ExpenseDate, ExpenseReportID FROM tblExpenseDetail WHERE ExpenseDate = #4/9/2011# AND ExpenseReportID = 99) Insert Into tblExpenseDetail (ExpenseDate, ExpenseReportID, ExpenseHotel) Values (#4/9/2011#, 99, 89);

[RuralGuy]

You need a separate SELECT query or Domain Function to test the record first.

[wideturn]

Thanks for the tip, I used dcount to check for the records and that did the trick. I was just stuck on trying to do it all with a single SQL statement and your tip got me out of that train of thought

[RuralGuy]

Excellent! Thanks for posting back with your success and glad we could help.