Dlookup problem 2

[scottian]

Im hoping someone can help. The code Im using is:-

------------------------------------
If (DLookup("[Date]", "GasFigures", "[Date]&quot) = Me![Text235] Then
DoCmd.OpenForm "AllRecords", acNormal, "", "", , acNormal
Else
DoCmd.OpenForm "NewRecord", acNormal, "", "", , acNormal
End If
------------------------------------

on the on click event of a button. i want it to open the all figures form if the date in the text box "text235" matches any record in the GasFigures table. if no match is found then the "NewRecord" form opens instead.

any help is greatly appreciated

 

[chirpyform]

try
dim Var
if not isNull(DLookup("[Date]", "GasFigures", "[Date]" = Me![Text235].Value)) then
DoCmd.OpenForm "AllRecords", acNormal, "", "", , acNormal
Else
DoCmd.OpenForm "NewRecord", acNormal, "", "", , acNormal
End If
chirpy

 

[scottian]

thanks for the fast response chirpyform

but the code still only opens the new form and my test data does contain the date in the text box. I should point out that the date field is a date format, does it have to be text or number. or does it not matter that the field is a date format?

 

[chirpyform]

try

msgBox Me!Text235.Value
'Check if the value is in the column [Date] of the table
'GasFigures
'I also changed the speach mark to being after the
'Me![Text235].Value which is important

if not isNull(DLookup("[Date]", "GasFigures", "[Date] = Me![Text235].Value&quot) then
DoCmd.OpenForm "AllRecords", acNormal, "", "", , acNormal
Else
DoCmd.OpenForm "NewRecord", acNormal, "", "", , acNormal
End If

 

[scottian]

tried that but get an error.
"the expression you entered as aquery parameter produced the following error: The object doesnt contain the automation object Me![Text235].Value "

which was in the following line:-
If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = Me![Text235].Value&quot) Then

i also tried putting the quotes back to the original position you gave, as:-
If Not IsNull(DLookup("[Date]", "GasFigures", "[Date]" = Me![Text235].Value)) Then

which gave no error but opened the new record form.

 

[chirpyform]

got it
If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = " & Me![Text235].Value)) Then

 

[chirpyform]

unless it is text then you need

If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = """ & Me![Text235].Value & "&quot) Then

but it is one of the two

 

[scottian]

Thanks for the attention you are giving this problem Chirpyform, but its still not working, the ammendment doesnt give any errors but only opens the new record form. i even tried to remove the 'Not' from 'Not IsNull' as i thought it was a double negative, but nothing changed.

 

[chirpyform]

You ran the same code 2 times: once with the not and once without and it did the same thing?????

Instead of using Me!Text235 use a value that is definitely in the table and then you will know where the error is.

If it works with the number you just have to check the text235

 

[scottian]

ive tried to change the date field to a text format and replaced the code with the last text option you gave but got a syntax error

 

[chirpyform]

not surprised you have to play with the quotes I never remember how many to put

 

[scottian]

Ive now switched to a text field "InputBy" in the table which holds the user iD, ive substituted the dlookup line in the code you gave with the text option you supplied but im getting a syntax error:-
runtime error '3075'
syntax error in string expression '[InputBy] = ''scott1i'

 

[chirpyform]

There really shouldn't be a problem. If you put in a value by default eg

If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = 03/07/2003&quot) Then

then you will see if the DLookup works then all you need to do is use the Access help file or just play with the number of quotes
With a numeric field (maybe with text as well) i'm sure that the following works

If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = " & Me![Text235].Value)) Then

 

[chirpyform]

I tried it myself and it is definitely

If Not IsNull(DLookup("[Date]", "GasFigures", "[Date] = " & Me![Text235].Value)) Then

test or numeric

Tell me if it works please (and a star never goes down badly)
chirpy

 

[rickgrimes]

Since [Date]is a date field, try:

If not isnull(Dlookup("[Date]","[GasFigures]","[Date]=#" & Me![Text235].Value & "#&quot) Then

my .02

Good Luck.

 

[chirpyform]

well spotted

 

[scottian]

would you mind if i sent you test copy of what im trying to do?

 

[chirpyform]

no
but this worked for me

ControleString = DLookup("libellé", "[8 Catalogue des fonctions]", "[Ident Fonction standard] = " & CONTROLE_DINTEGRITE)

libellé is the column with the result
[8 Catalogue des fonctions] is the table
[Ident Fonction standard] is a column
CONTROLE_DINTEGRITE is a const integer

 

[scottian]

the code is sort of working. the code doesnt produce an error but its not finding the date in the table, and the date does exist. if i rmove the &quot;not&quot; from the &quot;If not isnull&quot; then the code opends the form regardless of whether the date exists or not. this also happened when i substituded the = for <>.
please can i have your e-mail and you probably get it as soon as you see the database.

 

[chirpyform]

chris_chesters@hotmail.com