getting the name of the query?

[Geoff Cox]

Hello,

I have a created field in a query using,

invoice_number: "invoice_reminder_" & Format(Date(),"mmm-yy") & "_" &
[DCount] & DCount("*","[renewal-end-jan-08]","[ID] <=" & [ID])

and would like to generalise the query. Is it possible to reference
the name of the query (renewal-end-jan-08 in this case)?

Cheers

Geoff

 

[Douglas J. Steele]

No, it's not, unless you're generating the SQL for the query dynamically
using VBA.

However, having queries named "renewal-end-jan-08" doesn't sound like a good
idea to me. It implies that you're going to have dozens of different
queries: "renewal-end-jan-08", "renewal-end-feb-08", "renewal-end-mar-08"
and so on.

Why not have a query for all months, and pass the month in DCount as well?

 

[Geoff Cox]

No, it's not, unless you're generating the SQL for the query dynamically
using VBA.

However, having queries named "renewal-end-jan-08" doesn't sound like a good
idea to me. It implies that you're going to have dozens of different
queries: "renewal-end-jan-08", "renewal-end-feb-08", "renewal-end-mar-08"
and so on.

Why not have a query for all months, and pass the month in DCount as well?

Douglas,

I hadn't thought of that - how do I do that though?!

Cheers

Geoff

 

[Geoff Cox]

No, it's not, unless you're generating the SQL for the query dynamically
using VBA.

However, having queries named "renewal-end-jan-08" doesn't sound like a good
idea to me. It implies that you're going to have dozens of different
queries: "renewal-end-jan-08", "renewal-end-feb-08", "renewal-end-mar-08"
and so on.

Why not have a query for all months, and pass the month in DCount as well?

Douglas

Does this make sense? I have a series of queries which look for the
subscription start dates which were in the month a year before the
current month, using

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

The invoice number with a name based on the current month and name is
generated using,

invoice_number: "rtptv_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[renewal-end-dec-07]","[ID] <=" & [ID])

So, in fact no need for a query for each month or to pass the month in
the DCount as in any particular month the start dates for the month 12
months ago will be found and the invoice name will have the current
month and year in its name.

The only change would be in the name of the query, i.e.
renewal-end-dec-07 would become just renewal-query and be quite
general.

Am I totally wrong here?!!

Cheers

Geoff

 

[Douglas J. Steele]

No, it's not, unless you're generating the SQL for the query dynamically
using VBA.

However, having queries named "renewal-end-jan-08" doesn't sound like a
good
idea to me. It implies that you're going to have dozens of different
queries: "renewal-end-jan-08", "renewal-end-feb-08", "renewal-end-mar-08"
and so on.

Why not have a query for all months, and pass the month in DCount as well?

Douglas

Does this make sense? I have a series of queries which look for the
subscription start dates which were in the month a year before the
current month, using

Between DateSerial(Year(Date())-1,Month(Date()),1) And
DateSerial(Year(Date())-1,Month(Date())+1,0)

The invoice number with a name based on the current month and name is
generated using,

invoice_number: "rtptv_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[renewal-end-dec-07]","[ID] <=" & [ID])

So, in fact no need for a query for each month or to pass the month in
the DCount as in any particular month the start dates for the month 12
months ago will be found and the invoice name will have the current
month and year in its name.

The only change would be in the name of the query, i.e.
renewal-end-dec-07 would become just renewal-query and be quite
general.

Am I totally wrong here?!!

If I'm following you correctly, that sounds like you've got it.

 

[Geoff Cox]

If I'm following you correctly, that sounds like you've got it.

Thanks Douglas! I changed the date on the PC to January 2008 and it
worked OK.

Cheers

Geoff