DLookup Date

[Jameo]

Hi all,
I have a Table: tblDemandForcast
With Fields:
Date (as Date)
D -1 (as number)
D -2 (as number)
D -3 (as number)
D -4 (as number)
D -5 (as number)
D -6 (as number)
I then have a form, frmDemandForcasting, that has an unbound date field (txtRepDate) and fields D -1 through to D -6 (named in that style) bound to the tblDemandForcast.
I am trying to perform a check to see if a record exists for the date in txtRepDate, I have the code bewlow, but it does not seem to be working.
Could anyone help shed some light upon this?
Many thanks

If IsNull(DLookup("[Date]", _
                  "[tblDemandForcast]", _
                  "[Date]=#" & Me!txtRepDate & "#")) Then
    MsgBox "Duplicate!"
Else
    DoCmd.RunSQL "INSERT INTO [tblDemandForcast] " & _
                 "([Date],[D -1],[D -2],[D -3],[D -4],[D -5],[D -6]) " & _
                 "VALUES (#" & Me!txtRepDate & "#," & Me![D -1] & Me![D -2] & Me![D -3] & Me![D -4] & Me![D -5] & Me![D -6] & ")"
    Call Me.Requery
End If

 

[Norie]

What isn't working?

I can see 2 possible problems.

The first one could be your field name.

Date is a function in Access used to return today's date.

You can use it as a field name but it's not really a good idea.

The problem here could be that in the DLookup [Date] is being interpreted as the date function and not the field.

The other problem could be the format of the date in txtReptDate.

Access can be very particular about the format dates, even when you use #s to enclose the value.

You could try dropping the #s and using DateValue.

"[Date]=" &  DateValue(Me!txtRepDate)

or format Access will accept/understand.

"Date=#" & Format(Me!txtRepDate.Value, "mm/dd/yy" & "#"

 

[Jameo]

Thanks for the reply, I have changed the Date field name. I am now getting an error stating the input fields and query values are different. This is confusing, because I have check time and time again and the number of fields i have in my form, table and VBA match exactly :/

My code now is as follows:

Private Sub cmd_AddDF_Click()
On Error GoTo Err_cmd_AddDF_Click
If IsNull(DLookup("[Gas Day]", _
                  "[tblDemandForcast]", _
                  "[Date]=#" & Me!txtRepDate & "#")) Then
    DoCmd.RunSQL "INSERT INTO [tblDemandForcast] " & _
                 "([Gas Day],[D],[D -1],[D -2],[D -3],[D -4],[D -5],[D -6]) " & _
                 "VALUES (#" & Me!txtRepDate & "#," & Me![D] & Me![D -1] & Me![D -2] & Me![D -3] & Me![D -4] & Me![D -5] & Me![D -6] & ")"
    Call Me.Requery
    Else
    MsgBox "Duplicate"
End If
 
Exit_cmd_AddDF_Click:
    Exit Sub
Err_cmd_AddDF_Click:
    MsgBox Err.Description
    Resume Exit_cmd_AddDF_Click
    
End Sub

 

[Jameo]

I've made a couple of changes,

Table: tblDemandForcast
Fields: GasDay, D, D1, D2, D3, D4, D5, D6

Form: frmDemandForcasting
Fields: txtRepDate, txtD0, txtD1, txtD2, txtD3, txtD4, txtD5, txtD6


And my code:

Private Sub cmd_AddDF_Click()
On Error GoTo Err_cmd_AddDF_Click
If IsNull(DLookup("[GasDay]", _
                  "[tblDemandForcast]", _
                  "[GasDay]=#" & Me!txtRepDate & "#")) Then
    DoCmd.RunSQL "INSERT INTO [tblDemandForcast] " & _
                 "([Gas Day],[D],[D1],[D2],[D3],[D4],[D5],[D6]) " & _
                 "VALUES (#" & Me!txtRepDate & "#," & Me![txtD0] & Me![txtD1] & Me![txtD2] _
                 & Me![txtD3] & Me![txtD4] & Me![txtD5] & Me![txtD6] & ")"
    Call Me.Requery
    Else
    MsgBox "Duplicate"
End If
 
Exit_cmd_AddDF_Click:
    Exit Sub
Err_cmd_AddDF_Click:
    MsgBox Err.Description
    Resume Exit_cmd_AddDF_Click
 
End Sub

Still getting the same error - Number of query values and destination fields are not the same

 

[Norie]

You seem to be missing commas between each values in the VALUES part of the INSERT query.

Without the commas you've have va1val3val4 etc which is all the values concatenated together into one megavalue.

Try using & "," & between the values.

PS re your field names actually D -1, D -2 etc with the space?