help with syntax

[gls858]

I have this expression:

DateDiff("d",[InvoiceDate],Now())

I want to modify it to pull InvoiceDate from a specific
table ECNCMaster. I can't seem to figure out the bracketing.
Could someone give me a hand with syntax?

gls858

 

[John Vinson]

I have this expression:

DateDiff("d",[InvoiceDate],Now())

I want to modify it to pull InvoiceDate from a specific
table ECNCMaster. I can't seem to figure out the bracketing.
Could someone give me a hand with syntax?

gls858

That's because you cannot refer to fields in a table by just using the
table name.

Presumably ECNCMaster might have many records. How do you know which
record to retrieve???

I'd suggest

DateDiff("d", DLookUp("[InvoiceDate]", "[ECNCMaster]", <criteria to
select the correct record>), Date())

Now() returns the current date and time accurate to microseconds - you
don't need the time, just the date.

John W. Vinson[MVP]

 

[gls858]

I have this expression:

DateDiff("d",[InvoiceDate],Now())

I want to modify it to pull InvoiceDate from a specific
table ECNCMaster. I can't seem to figure out the bracketing.
Could someone give me a hand with syntax?

gls858

That's because you cannot refer to fields in a table by just using the
table name.

Presumably ECNCMaster might have many records. How do you know which
record to retrieve???

I'd suggest

DateDiff("d", DLookUp("[InvoiceDate]", "[ECNCMaster]", <criteria to
select the correct record>), Date())

Now() returns the current date and time accurate to microseconds - you
don't need the time, just the date.

John W. Vinson[MVP]

Thanks for the help John. One problem was I was trying to pull the date
form the wrong table...Doooh. I'm actually trying to pull the Invoice date
for an invoice in a accounts rec. table. I'm modifying someone else's
query. When I added another table to the query it also contained the
invoice number. Access said it didn't know which value to use. the
following expression seems to work:

DateDiff("d",[ECNAMaster]![InvoiceDate],Now())

Is this method incorrect?

I wasn't aware about the now() function. You're right that degree of
accuracy is not needed.

thanks for the help,

gls858

 

[John Vinson]

John W. Vinson[MVP]

Thanks for the help John. One problem was I was trying to pull the date
form the wrong table...Doooh. I'm actually trying to pull the Invoice date
for an invoice in a accounts rec. table. I'm modifying someone else's
query. When I added another table to the query it also contained the
invoice number. Access said it didn't know which value to use. the
following expression seems to work:

DateDiff("d",[ECNAMaster]![InvoiceDate],Now())

Is this method incorrect?

If ENCAMaster is included in your Query, it should be okay.

I wasn't aware about the now() function. You're right that degree of
accuracy is not needed.

I'd use Date() in the expression above for the same reason - you don't
need microsecond accuracy from your system clock if you're just
counting days!

John W. Vinson[MVP]

 

[gls858]

John W. Vinson[MVP]

Thanks for the help John. One problem was I was trying to pull the date
form the wrong table...Doooh. I'm actually trying to pull the Invoice date
for an invoice in a accounts rec. table. I'm modifying someone else's
query. When I added another table to the query it also contained the
invoice number. Access said it didn't know which value to use. the
following expression seems to work:

DateDiff("d",[ECNAMaster]![InvoiceDate],Now())

Is this method incorrect?

If ENCAMaster is included in your Query, it should be okay.

I wasn't aware about the now() function. You're right that degree of
accuracy is not needed.

I'd use Date() in the expression above for the same reason - you don't
need microsecond accuracy from your system clock if you're just
counting days!

John W. Vinson[MVP]

Thanks. I did change it on my query. Just pasted the old version from my
clipboard.

gls858