DCount syntax problems

[NewfieGolfer]

Hi all,
Here's what I have:

recordnum = DCount("*", "MyTable", "[TableField] =" & StartDate )

StartDate is defined earlier in my code. Right now, i'm getting Zero for recordnum when there are 6 records in my table that have [TableField] = StartDate.

Is this a syntax problem or am i missing something?

Thanks,
NG

 

[jebry]

Hi NG!

How is the field in your table populated? If it is done automatically using the Date() function, then the value you have stored includes a time portion even if it isn't displayed and will ruin any chance for equality in your criteria. Another question, what is StartDate, is it a field in a table, a variable in code, or a textbox on a form?

hth
Jeff Bridgham
bridgham@purdue.edu

 

[NewfieGolfer]

Jeff,
The value in the table field is populated from a form. Yes, eventually the field will have a date and time portion but right now i'm just entering the values directly into the table with just the date portion (for testing purposes), so there shouldn't be an equality problem. If i use this code:

recordnum = DCount("*", "MyTable", "[TableField] = #5/20/2002#" )

Then it works. But, by using the variable, which is defined earlier as StartDate = #5/20/2002# it doesn't work. This makes no sense to me because they're basically the same. The reason I need to use a variable is because I'm using a for loop to increase this date by one day on each loop. By doing this, i want to be able to count the number of records in my table for successive days. So on 5/20/2002 there may be 6 records, on 5/21/2002 there may be 10 records and so on, eventually placing the date and number of records in a seperate table (but tha's a whole new thing).

The variable (StartDate) is defined in code and not a field or textbox.

There's tons more stuff i need to do with this, but i'll leave that for another time. Take it one step at a time.

Any help appreciated,
NG

 

[jebry]

Hi!

Is StartDate defined as a string? The domain aggregate functions require strings for there arguments.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[NewfieGolfer]

Jeff,
I just did what u said. I defined it as a string, no luck. I then changed it to StartDate= "5/20/2002" and still didn't work. It's still comin up with no records matching 5/20/2002 when there's 6 in my table.

So my code is like this now:

recordnum = DCount("*", "MyTable", "[TableField] =" & StartDate )

with StartDate defined as a string.

Any more ideas?
NG

 

[jebry]

Hi!

Just one, you can try:

recordnum = DCount("*", "MyTable", "[TableField] = #" & StartDate & "#&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[NewfieGolfer]

Thanks Jeff, it works now.

I was wondering if u can tell me how i would copy the Startdate and recordnum to a different table. The table just has two fields called StartDate and RecordNum. Right now, i'm getting a "can't assign value" type error.

Any help appreciated,
NG