Another Dlookup Question (1 Viewer)

[Taff]

Hi all,

Have tried searching the forums but to no avail.

I have a table the records imports to the database. It records the Name, Time Created, Date Created, Time Imported and Date Imported.

When I import the a file to the database the Name, Time Created and Date Created are stored in unbound text boxes on my forms.

What I am trying to do is check if the file I am trying to Import has already been imported.

The table which I record the Import details to is called tblImportDetails and the fields are as follows:-

DatasetName - Text
CreationDate - Short Date
CreationTime - Long Time
DateImported - Short Date
TimeImported - Long Time

My unbound fields on my forms are called txtCreationDate and txtCreationTime these are also in the format of Short Date and Long Time.

I have the following Code behind a command button:-

Dim VarCheckDate As Variant, VarCheckTime As Variant, VarDataset As Variant

VarCheckDate = DLookup("[CreationDate]", "ImportDetails", "[CreationDate] = " & Me!txtCreationDate & "")
VarDataset = DLookup("[Datasetname]", "tblImportDetails", "[Datasetname] = '" & Me!txtDatasetName & "'")
VarCheckTime = DLookup("[CreationTime]", "ImportDetails", "[CreationTime] = #" & Me.txtCreationTime & "#")

If VarCheckDate = Me.txtCreationDate And VarCheckTime = Me.txtCreationTime And VarDataset = Me.txtDatasetName Then
MsgBox "Already Imported"
End if

However when I Click the command button nothing happens and I know the values do exist and are the same.

Am not sure if I am on the right track so any help is greatly appreciated.

Anthony.

 

[adaytay]

Sometimes a fresh pair of eyes is required...

I spotted this - particularly the bits in red...

VarCheckDate = DLookup("[CreationDate]", "ImportDetails", "[CreationDate] = " & Me!txtCreationDate & "")
VarDataset = DLookup("[Datasetname]", "tblImportDetails", "[Datasetname] = '" & Me!txtDatasetName & "'")
VarCheckTime = DLookup("[CreationTime]", "ImportDetails", "[CreationTime] = #" & Me.txtCreationTime & "#")

Methinks the bits I've highlighted should be "tblImportDetails" instead.

HTH,

Ad

 

[Taff]

Hi Adaytay,

I have changed the mistakes but unfortunately still nothing happens.

Thanks for the reply.

Ant.

 

[Electric Ant]

How about something like.......

Dim cnndb As Connection
Dim rst1 As ADODB.Recordset
Dim sql$
Dim sql1$
Dim sql2$
Dim sql3$

sql1$ = "SELECT * FROM tblImportDetails WHERE [CreationDate] = " & Me!txtCreationDate
sql2$ = " AND [DatasetName] = " & Me!txtDatasetName
sql3$ = " AND [CreationTime] = " & Me!txtCreationTime

sql$ = sql1$ & sql2$ & sql3$

Set cnndb = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
rst1.Open sql$, cnndb, adOpenKeyset, adLockOptimistic

If rst1.RecordCount>0 then 'MsgBox "Already Imported"

 

[Taff]

Syntax Error (Missing Operator)

Hi Electric Ant,

Thanks for the relply, think i'm getting closer.

I now get the message:-

Syntax error (missing operator) in query expression '[creationdate] = 27/10/2004 AND [DatasetName] = Learner Dataset AND [CreationTime] = '14:31:41'.

Any Ideas,

Ant.

 

[Electric Ant]

Do you need single quotes in there somewhere?

like

sql2$ = " AND [DatasetName] = ' " & Me!txtDatasetName & "'"

 

[Taff]

Message now appearing:-

I now get the message:-

Syntax error (missing operator) in query expression '[creationdate] = 27/10/2004 AND [DatasetName] = 'Learner Dataset' AND [CreationTime] = '14:31:41'.

 

[Electric Ant]

I'm quite new to this Access malarky and not very clued up about SQL, but try breaking up the SQL statement to see which bit is causing the problem

ie. just execute sql1$ first then sql$= sql1$ & sql2$, then sql$=sql1$ & sql2$ & sql3$

In your original code you had # 's in it?? Is that anything to do with it??