Need to check if record exists or not

[bensplace]

We want to write product logs at work one day log and one night log
I have a date field and a day/night field

there will always be two records with the same date, one will be a day record and one will be a night record
i need to check if a record exists eg. date + day or date+night.
the idea is that if it does exist the edit the record if need but if it does not exists the create a new record.

Easy eh!...........well no it is not easy.

I have this code but strSQL doesnty seam to compile.
I am wondering if it has any thing to do with the fact that date is a date and day/night is a text field. weater that is the case or not i cant work this one out at all

I have uploaded a database so you can see what i am on about


Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String

' quick way to get the current database we are runnig in
Set db = CurrentDb

' build your string
strSQL = "SELECT * FROM records WHERE records.Date1 = #" + Date1.Value + "# AND records.[Day/Night] = '" + Combo0.Value + "'"

' execute the query
Set rs = db.OpenRecordset(strSQL)

If (rs.RecordCount > 0) Then
MsgBox "This record already exists", vbInformation
Else
MsgBox "No record exists", vbInformation
End If




I did alot of programming about 12 years ago usibg VB4 but alot has change since the

if you cant help then a pointer in the right direction would be grate

Thanks for any help


I have even tryed this but no luck with this either

By the way I am using ms access 2007



If (Text2 & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "[Date1]=""" & Text2 & """"
If rs.NoMatch Then
MsgBox "Sorry, no such record '" & Text2 & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close

 

[jdraw]

This iif statement should determine if a record for today exists or not

IIF(DCount("*","Records","Date1 =" & Date()) >0,"Todays Record Exists","Todays Record Does NOT Exist")

 

[bensplace]

A bit of a update. I have now been able to check for the date and it works.
when it finds a valid date i then ask if my combo0 = day/night in the if statment but an error comes up " TYPE MISMATCH"


Any ideas?

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
With rs

.FindFirst "[Date1] = #" & Format(Me![Text2], "mm\/dd\/yy") & "#"
If .NoMatch Then
MsgBox "No Match"
Else

If "[Day/Night] = """ & Me![Combo0] & """" Then (this is the line i have the error in)

MsgBox "found it"
End If
Me.Bookmark = .Bookmark
End If
End With



I have even put them together but still type mismatch
.FindFirst "[Date1] = #" & Format(Me![Text2], "mm\/dd\/yy") & "#" And "[Day/Night] = """ & Me![Combo0] & """"


Both these work well on there own , but do u think i can get them to work together.

.FindFirst "[Date1] = #" & Format(Me![Text2], "mm\/dd\/yy") & "#"
.FindFirst "[Day/Night] = """ & Me.Combo0 & """" Then