Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2008
    Location
    Hayden CO
    Posts
    5

    Unanswered: Hi and having trouble with dcount

    Hi everybody:

    Found this forum because I'm having trouble with dcount. I have a query with a parameter for a date. Basically a daily report. I want to count the records for that day, but I keep getting an error for the count. I know it has something to do with the parameter and I'm looking for a workaround. Here is the expression;
    =DCount("[MeterID]","MeterTest Query","userfield_1=#")

    MeterId is the field i want to count
    MeterTest query is the query
    Userfield_1 is the date field

    What am I doing wrong?

    Thanks in advance

    rod

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    a '#' won't work as a date. it's what you put around a date. so maybe something like:
    Code:
    =DCount("[MeterID]","MeterTest Query","userfield_1=#" & Date & "#")
    to the function, that would look like "userfield_1=#2/20/2008#" which is what you want

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    there is no "date field" in Access.
    have a go with the following and maybe one will work depending on what you are really doing:
    ..."userfield_1 = " & Date())
    ..."userfield_1 = #" & format$(now(), "mm/dd/yyyy") & "#")
    ..."userfield_1 = '" & format$(now(), "zzzzzzzzzzzz") & "'") where zzzzzzzzzzzzz is whatever you used as a format if you are storing your "date" as text.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Feb 2008
    Location
    Hayden CO
    Posts
    5
    None of those Ideas worked, I still get the #error in the report. Userfield_1 is a text field that we just put the date in.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Benniehaha
    Userfield_1 is a text field that we just put the date in.
    That's a mistake for starters.
    What does the below return?
    Code:
    SELECT DISTINCT userfield_1 
    FROM [MeterTest Query] 
    WHERE ISDATE(userfield_1) = 0
    Also - please could you post the code you are referring to in your last post that did not work?

  6. #6
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    just to double check (and i'm not sure if access is case sensitive in these instances), but you put userfield_1 in your code (and i followed suit), but you have Userfield_1 capitalized in your explanation.

  7. #7
    Join Date
    Feb 2008
    Location
    Hayden CO
    Posts
    5
    Pootle Fump
    All of them
    The text date is from a meter test program that saves the info to access .mbd file. I don't think I can change that but not sure.

    I get a Compile error.in query expression 'ISDATE(UserField_1)=0'. Also when I hit help it gives error code 3075.

    PS There is a field that is a date/time field. Not sure if that would make a difference.

    jmahaffie
    I redone everything with the capitalization and I still couldn't get it to work.
    Last edited by Benniehaha; 02-20-08 at 16:18.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You copied and pasted and ran it? If not - please could you? What version of Access?

  9. #9
    Join Date
    Feb 2008
    Location
    Hayden CO
    Posts
    5
    It is version Access 2003 SP3
    I copied and pasted it to a new query called test. It runs and then it shows the userfield_1 looks like a field but nothing underneath it. I have a .doc that shows the print screen. I think I have it attached.

    Thanks

    Rod
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's good - it means that all your strings can be converted to valid dates. The point is when you store "dates" in text columns you are not storing dates - you are storing text that looks like a date to us, but not to a PC. What that often means is that you get junk in the field (text that cannot be converted to valid dates). The query returns all text that cannot be converted and it returned nothing.

    Anyway - good news. Please could you answer the second question in post 5 of this thread?

  11. #11
    Join Date
    Feb 2008
    Location
    Hayden CO
    Posts
    5
    Not sure if this is what you are looking for.
    SELECT MeterTest.MeterID, MeterTest.Form, MeterTest.Base, MeterTest.Volts, MeterTest.Amps, MeterTest.AF_SF, MeterTest.AF_SP, MeterTest.AF_SL, MeterTest.AL_SF, MeterTest.AL_SP, MeterTest.AL_SL, MeterTest.UserField_1, MeterTest.UserField_2, MeterTest.UserField_3, MeterTest.UserField_4, MeterTest.UserField_5, MeterTest.UserField_6, MeterTest.UserField_7, MeterTest.CreateDate
    FROM MeterTest
    WHERE (((MeterTest.UserField_1)=[Enter Date:]));
    This is for the query.
    =DCount("[MeterID]","MeterTest Query","UserField_1=#" & [Date] & "#")
    This is the expression in the report

    The attachment is a print screen of the report
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •