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

Posted on 2006-05-15
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
    LVL 65

    Accepted Solution

    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

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

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

    Expert Comment

    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

    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

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Cascading Multiselect Listbox 3 28
    query error in access 2010 dao 3.6 5 28
    Running Sum Help 31 22
    dcount multiple criteria 19 22
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now