Conditional Formating

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!
I have a query where I want to format a field called “InvoiceNrâ€, according
to the current year and assign a value to all previous years. e.g.:
If the “InvoiceNr†is 103 and I generated the invoice in 01.01.2005, then I
will have a new value 2005/103
For this I use this format: Nr-Invoice: Rechts(Jahr([InvoiceDate]),4) & "/"
& ([InvoiceNr])
My problem is that for all invoices created before 01.01.2005, I want them
to appear with a 2004/. e.g.:
If the “InvoiceNr†is 19 and I generated the invoice in 01.10.2003, then I
will have a new value 2004/19
What is missing in my code?
Thanks for your help
 
viddom said:
Hello!
I have a query where I want to format a field called
Click to expand...
“InvoiceNrâ€, according
to the current year and assign a value to all previous years. e.g.:
If the “InvoiceNr†is 103 and I generated the invoice in 01.01.2005, then I
will have a new value 2005/103
For this I use this format: Nr-Invoice:
Click to expand...
Rechts(Jahr([InvoiceDate]),4) & "/"
& ([InvoiceNr])
My problem is that for all invoices created before 01.01.2005, I want them
to appear with a 2004/. e.g.:
If the “InvoiceNr†is 19 and I generated the invoice in 01.10.2003, then I
will have a new value 2004/19
What is missing in my code?
Thanks for your help
Click to expand...

viddom,

The following is untested (due to differences in regional time
settings; and I am not certain what the Rechts and Jahr functions
are).

If I'm right, Jahr() is the Year() function, and Rechts() is the
Right() function.

Since Jahr() will produce the 4-digit year, why take the
"Rechts(<date>,4)" of an already 4-digit number?

Try:

IIF( Jahr([InvoiceDate])) < 2005, "2004", Jahr([InvoiceDate])) ) & "/"
& [InvoiceNr]


Sincerely,

Chris O.
 
Thanks for the details, it works very well.

Chris2 said:
viddom said:
Hello!
I have a query where I want to format a field called
Click to expand...
“InvoiceNrâ€Â, according
to the current year and assign a value to all previous years. e.g.:
If the “InvoiceNr†is 103 and I generated the invoice in 01.01.2005, then I
will have a new value 2005/103
For this I use this format: Nr-Invoice:
Click to expand...
Rechts(Jahr([InvoiceDate]),4) & "/"
& ([InvoiceNr])
My problem is that for all invoices created before 01.01.2005, I want them
to appear with a 2004/. e.g.:
If the “InvoiceNr†is 19 and I generated the invoice in 01.10.2003, then I
will have a new value 2004/19
What is missing in my code?
Thanks for your help
Click to expand...

viddom,

The following is untested (due to differences in regional time
settings; and I am not certain what the Rechts and Jahr functions
are).

If I'm right, Jahr() is the Year() function, and Rechts() is the
Right() function.

Since Jahr() will produce the 4-digit year, why take the
"Rechts(<date>,4)" of an already 4-digit number?

Try:

IIF( Jahr([InvoiceDate])) < 2005, "2004", Jahr([InvoiceDate])) ) & "/"
& [InvoiceNr]


Sincerely,

Chris O.
Click to expand...
 
Back
Top