Problems with DLookup and an If Statement

[Bonediggler]

Hi there,

I am trying to use a DLookup function to scan an existing table for a specified date value. If the value is found, the procedure will terminate with a message box. If the value is not found, the procedure will continue and import the specified Excel sheet.

The code I wrote to do the above keeps giving me a type-mismatch error:

Dim LookupMonth As Integer
Dim LookupDay As Integer
Dim LookupYear As Integer
Dim LookupFullDate As Date

LookupDay = Right(txtValues.Value, 2)
LookupYear = "2007"
NewValue = txtValues.Value & "!"

(code)


Select Case Left(txtValues.Value, 3)

Case "Sep"

LookupMonth = 9

End Select


LookupFullDate = CDate(LookupMonth & "/" & LookupDay & "/" & LookupYear)
BooleanValue = IsNull(DLookup("[Date]", "tbl_DNIS", "Date =#" & LookupFullDate & "#"))

If BooleanValue Is True Then
Resume ExecuteLine
Else
MsgBox ("You are about to reimport existing data")
Resume ExitLine
End If

(code)

Any help is greatly appreciated.

 

[lewds]

Hi...

Seems overly complicated to allow the user to fill in a textbox with any date format they might want and then try to write code to compensate.

Here is something you might try.......

Use a calendar control to select a date

convert the date to a function

use the function in your DLOOKUP statement

use an IF construct to test the return value


<code> in a standard module

Global SelectedDate as Date

Public Function FnSelectedDate()
FnSelectedDate = SelectedDate
End Function

<end code>

<code> Form

Private Sub Calendar1_Click()
SelectedDate = Me.Calendar1.Value
End Sub

Private Sub Command0_Click()
Dim CheckDate As Variant
CheckDate = DLookup("[Date]","tbl_DNIS" , "[Date] = FnSelectedDate()")

If IsNull(CheckDate) Then
Resume ExecuteLine
Else
MsgBox ("You are about to reimport existing data")
Resume ExitLine
End If
End Sub
<end code>

Click a date on the calendar then click the command button.

You were missing a set of brackets in your DLOOKUP statement around the [Date] in the where portion.

This method will work as long as the Date field in the table is a date and not a string.

 

[PHV]

FYI Resume is a valid instruction in an Error Handler routine only ...

 

[Bonediggler]

All--

The code I had in there works ok as the date entered in the text box has a custom input mask.

The problem ended up being changing If BooleanValue IS False to If BooleanValue = False.

I changed Resume to GoTo as well.

Thank you for the help!