Filter produces 2427: Expression has no value (1 Viewer)

T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
I've got a form which, on loading, sets a filter
Me.Filter = "Arrival>=" & SQLdate(dWhen) & " AND Arrival<=" & SQLdate(dLast)
Me.FilterOn = True

(SQLdate is a function which formats the date for me)

When I examine what it's supposed to be setting it shows me
Arrival>=#10/04/2010# AND Arrival<=#10/09/2010#

When the filter is turned on AND there are no records selected I get the error 2427, "You have entered an expression that has no value" and I'm not even able to trap this error with the normal 'On Error' stuff.

I click the OK button and everything works fine, the empty form opening as expected.

I've tried changing the filter rule but get the same effect, even when I make it really simple, which leads me to suspect that it may be something to do with the form.

I have another similar form working with a different table and different criteria on the filter but which has no objections to there being no records so what is it complaining about?

Maybe it's something strange with the way Access VBA has coded the statement?

Any ideas?

it's Access 2003 BTW
 
T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
'cos I never thought of it ;) Thanks for that, it's a lot easier to read.

Although it doesn't do anything to solve my problem.
 
DCrake

DCrake

Remembered
Local time
Today, 10:43
Joined
Jun 8, 2005
Messages
8,632
Because the filter does not return any records then the form does not effectively have a recordsource, and as such does not have any fields. This is the root or the error "expression has no value". What you might want to do is to do a DLookup on the underlying query/table using the date range. If it returns no records then set the filter to Select * From x where False. Otherwise use the filter option as planned.
 
T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
to vbaInet, Arrival is Date/Time

DCrake: What I tried was...

sSQL = "Arrival BETWEEN " & SQLdate(dWhen) & " AND " & SQLdate(dLast)
If DCount("*", "BedroomsWaiting", sSQL) = 0 Then
Me.Filter = "False"
​
Else
Me.Filter = sSQL
​
End If
Me.FilterOn = True

but I get exactly the same effect - error 2427.

Also, it doesn't explain why my other form, on which this one was based, doesn't have this problem. Maybe it's because it WAS based on that other form and there's some vestige of the previous table buried within its internals?
 
DCrake

DCrake

Remembered
Local time
Today, 10:43
Joined
Jun 8, 2005
Messages
8,632
Maybe it may be more prudent to supply the date criteria directly to the query as opposed to the form.

Maybe take a look at this link for inspiration.
 
boblarson

boblarson

Smeghead
Local time
Today, 02:43
Joined
Jan 12, 2001
Messages
32,059
Are you sure you have values ALWAYS in dWhen and dLast? If a null gets in there it can cause a problem.
 
V

vbaInet

AWF VIP
Local time
Today, 10:43
Joined
Jan 22, 2010
Messages
26,374
I think the error is happening in your SQLDate() function. You're probably trying to get the value of an object that hasn't been initialised. Let's see the code.

Is one of the fields of your form's underlying record source based on a function? Let's see the sql.
 
T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
It was nothing to do with the functions or logic I showed you but to an event attached to one of the fields on the form.

The Arrival field had an 'On Enter' event and that seemd to be throwing it. Why it's showing up on form load when I specify the filter I've no idea! Anyway, I've changed it to 'On Click' and now the problem's gone away. I guess 'On Enter' is triggered when the form loads and there was no field for it to enter, whilst at least 'On Click' waits for the user to do something.

Thanks for all the suggestions which got me thinking more clearly. Eventually I just started rebuilding the form again from scratch, copying and pasting the code and controls from the old version, until eventually it failed.
 
boblarson

boblarson

Smeghead
Local time
Today, 02:43
Joined
Jan 12, 2001
Messages
32,059
You do know what On Enter means, don't you? It's meaning is roughly synonymous to entering a doorway. Many people mistake it for hitting the Enter (Return) key.
 
T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
Yes, I do know what it means. ;)

It was supposed to activate if the user tabbed through the list of records, and worked fine if there were records, but gave 2427 on form load if there were none. I could equally have used the 'On Got Focus' event but that suffers from exactly the same 2427 problem.

Using the click event allows the form to load OK but doesn't give me the functionality I wanted so it's back to the drawing board!
 
boblarson

boblarson

Smeghead
Local time
Today, 02:43
Joined
Jan 12, 2001
Messages
32,059
Well you should be able to use the same event but do a test to see if there are records first before running the rest of the code.
 
T

trentham

Registered User.
Local time
Today, 10:43
Joined
Mar 27, 2010
Messages
11
Ah, of course!

I've been concentrating on where the failure was occurring, i.e. the form load event, but if I do the record count test in the 'On Enter' event then it's sorted!

Many thanks. I knew I was missing something blindingly obvious!
 
You must log in or register to reply here.

Similar threads

MattBaldry
Solved Open Form and Apply Filter to Subform
Replies
3
Views
85
theDBguy
theDBguy
B
Solved Filtering Subform based off carried over data
Replies
5
Views
336
Pat Hartman
P
G
Error 2427 : You entered an expression that has no value.
Replies
3
Views
380
GaP42
G
N
Buttons to filter form
Replies
7
Views
660
MajP
MajP
N
Filtering a data entry continuous form in MS Access / Cloud Database
Replies
6
Views
583
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom