Error 2427 You have entered an expression that has no value.

Posted on 2006-05-15
Medium Priority
Last Modified: 2012-08-13
I have a form that I'm trying to open with a filter to pull all records greater than a tempdate - 30 days.  "j" & "i" are what I've been using to see what value it gets for those fields.  The report open with no filters.  If I test for j or i I keep getting this error:  
Error 2427 You have entered an expression that has no value.  

There are no null values in these fields and when the report opens those fields have value.  I can't figure out why I keep getting this error.  Everything in here is working other than I can't see a value for MaxDateJ until after the report opens.  I have another report similar to this one that is using the same recordsource and it works fine.  I don't know where to go from here.  

Dim sFilter As String
Dim g As Date
Dim h As String
Dim i As Date
Dim j As String

DoCmd.OpenForm "DateF", , , , , acDialog, "ActiveJobDailyR"

g = Forms![DateF]![TempDate]
h = g - 30
If (Forms![DateF]![TempDate] = "") Then
    Me.FilterOn = False
    'j = Me.JobNumber
    i = Me.MaxDateJ
    sFilter = "[MaxDateJ]>=" + h + ""
    Me.FilterOn = True
End If
Question by:SCS1ST
  • 3
  • 2
LVL 65

Accepted Solution

rockiroads earned 2000 total points
ID: 16685502
Use DateAdd for date calculations

is h supposed to be current date - 30 days
h = DateAdd("d",-30,g)

then filter, u can do this instead

sFilter = "[MaxDate] >= #" & DateAdd("d",-30, Forms![DateF]![TempDate])

instead - but still check for null date

Author Comment

ID: 16685604
h = tempdate - 30 days.  So would it be:

sFilter = "[MaxDate]>= "& DateAdd("d", -30, Forms!DateF![TempDate])      ?  What is the # for?
LVL 65

Expert Comment

ID: 16685681
The # - that is used for checking string dates against a date field, but usually on a string
I realise my typo

It should be  sFilter = "[MaxDate]>= #" & DateAdd("d", -30, Forms!DateF![TempDate]) & "#"

alternative is to use Format

Using DateAdd here saves u having to set the variable h


Author Comment

ID: 16685966
This is what I eventually needed:
sFilter = "[MaxDateJ]>" & "'" & DateAdd("d", -30, Forms!DateF![TempDate]) & "'"
   Me.Filter = sFilter

Now here's the real kicker.  I initially forgot to add the me.filter=sfilter so that's why it wasn't going to the filter.  But still I learned a lot.  Starting to wonder when I'll finally get the hang of all this.  

Thanks so much for your help.

LVL 65

Expert Comment

ID: 16686027
Cool, glad u got it working. DateAdd is a good function to have, as u can clearly see
the other, just in case u wanted to create a date is


pass in year, month, day in numbers

DateSerial (2002, 11, 1)

returns a date

good luck, you almost there already as you tackling VBA :)

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question