How to compare date in table with current date

[ismith]

Hi,

im making a form which calculates the total of a till at the end of the day. Once this is saved i enter it in a database called 'tblEndofDayTotal'.

What i want to do is:

Ive got a button which links to this form.
So when the button is pressed, I want it to check the tblEndofdayTotal table and compare the date with todays current date. If this matches I want the fields to auto populate with the data of the matched field.
If it doesnt, I want it to leave the fields empty.

In my tblEndofDayTotal I have the following fields:

Date = Date/Time
DateTotal = Currency
TillTotal = Currency
Difference = Currency

This is what ive tried

Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE Date = " & Date)
 
If saveTotal.RecordCount = 0 Then
 
pre-fill all txt boxes
 
Else
 
Leave empty
 
End If

This code is not workin and its not matching the dates in the table.
How can i do this?

Thanks.

 

[RainLover]

WHERE Date = " & Date

Date returns ToDay's date.

So you have ToDay's date =ToDay's date

Date is a Reserved word. As you can see its use as a Field Name is incorrect.

Try MyDate instead.

You can search Reserved Words for Access in Google for a complete list of reserved words.

 

[ismith]

Thanks for the reply. I've tried this and its nt working.

​

This is my code:

Set db = CurrentDb
Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate = " & Date)
Set endTotal = db.OpenRecordset("EndofDayTotal")
 
If saveTotal.RecordCount = 0 Then
txtTotal.Value = endTotal("SumOfPaid")
 
ElseIf saveTotal.RecordCount > 0 Then
txtTotal.Value = saveTotal("DateTotal")
txtTillTotal.Value = saveTotal("TillTotal")
txtDifference.Value = saveTotal("Difference")
txtTotal.Locked = True
txtTillTotal.Locked = True
txtDifference.Locked = True
btnCalculate.Enabled = False
btnSave.Enabled = False
 
End If

 

[ismith]

Any ideas? i really need some help urgently

 

[RainLover]

Where did you get this code. It is something I have never seen before and I can't fix.

But I will have a closer look in a few hours.

First please post the full code.

Must sleep now.

 

[ismith]

I wrote the code myself.

This if my full code for the whole page:

Dim db As Database
Dim endTotal As DAO.Recordset
Dim saveTotal As DAO.Recordset
Dim strsql As String


Private Sub Form_Load()

Set db = CurrentDb
Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate = " & Date)
Set endTotal = db.OpenRecordset("EndofDayTotal")

If saveTotal.RecordCount = 0 Then

txtTotal.Value = endTotal("SumOfPaid")

ElseIf saveTotal.RecordCount = 1 Then

txtTotal.Value = saveTotal("DateTotal")
txtTillTotal.Value = saveTotal("TillTotal")
txtDifference.Value = saveTotal("Difference")

txtTotal.Locked = True
txtTillTotal.Locked = True
txtDifference.Locked = True
btnCalculate.Enabled = False
btnSave.Enabled = False

End If

End Sub
------------------------------------------------------------------------

Private Sub btnCalculate_Click()

If Trim("" & txtTillTotal.Value) = "" Then
MsgBox "Enter Till Amount", vbCritical, "Error"
txtTillTotal.SetFocus
txtDifference.Value = Null

ElseIf txtTillTotal.Value = 0 Then
MsgBox "Amount must exceed Zero", vbCritical, "Error"
txtTillTotal.SetFocus
txtTillTotal.Value = Null
txtDifference.Value = Null

ElseIf txtTillTotal.Value <= 0 Then
MsgBox "Amount must exceed Zero", vbCritical, "Error"
txtTillTotal.SetFocus
txtTillTotal.Value = Null
txtDifference.Value = Null

Else
txtDifference.Value = txtTillTotal.Value - txtTotal.Value
btnSave.Enabled = True
    If txtDifference.Value > 0 Then
    MsgBox "There is an Overpayment of " & "£" & txtDifference.Value, vbCritical, "OverPayment"
    ElseIf txtDifference.Value < 0 Then
    MsgBox "There is a Deficit of " & "£" & txtDifference.Value, vbCritical, "Deficit"
    ElseIf txtDifference.Value = 0 Then
    MsgBox "Total Amount is correct", vbInformation + vbOKOnly, "Confirmation"
    End If
End If

End Sub

----------------------------------------------------------------------
Private Sub btnSave_Click()

Set db = CurrentDb
Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate = " & Date)

If saveTotal.RecordCount = 0 Then

saveTotal.AddNew

saveTotal("MyDate") = txtDate.Value
saveTotal("DateTotal") = txtTotal.Value
saveTotal("TillTotal") = txtTillTotal.Value
saveTotal("Difference") = txtDifference.Value

saveTotal.Update
saveTotal.Close
btnSave.Enabled = False
txtTillTotal.Locked = True
btnCalculate.Enabled = False

MsgBox "Details have been saved", vbInformation + vbOKOnly, "Confirmation"

Else

MsgBox "Details have already been saved", vbCritical, "OverPayment"

End If

End Sub

 

[RainLover]

Where does this fail and what error message are you getting.

 

[ismith]

Im not getting an error message.

Its this part of the code thats not working:

Set db = CurrentDb
Set saveTotal = db.OpenRecordset("SELECT * FROM tblEndofDayTotal WHERE MyDate = " & Date)
Set endTotal = db.OpenRecordset("EndofDayTotal")

If saveTotal.RecordCount = 0 Then

txtTotal.Value = endTotal("SumOfPaid")

ElseIf saveTotal.RecordCount = 1 Then

txtTotal.Value = saveTotal("DateTotal")
txtTillTotal.Value = saveTotal("TillTotal")
txtDifference.Value = saveTotal("Difference")

txtTotal.Locked = True
txtTillTotal.Locked = True
txtDifference.Locked = True
btnCalculate.Enabled = False
btnSave.Enabled = False

End If

When the form loads, I want it to check in the tblEndofDayTotal to see if there is already the same date in the table. If it is, i want the text boxes to populate, If its not, i want them to stay empty.

When i add it in the table and clikc on the form again, Its not pre-populating like its meant to do.

 

[RainLover]

Adding some error trapping would help.

The other thing you could do is to see if the values you are expecting actually happen.

You can do this with a message box that displays each Value.

Stepping through the code is another good tool. This will allow you to run the cursor over a line that has been executed and check the values.

They will show up in Yellow.

It is important to track down the error. At the moment all you know is the the whole procedure FAILS somewhere.

This could be a good learning curve for you.

Also have you declared

Option Compare Database
Option Explicit

If you haven't then please do so and then see if your code compiles.