Building Expression with 2 different field types & format stay the

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

Guest

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
 
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")
 
Thank you very much!!!! I knew it was probably really simple but I haven't
worked on Access in about 12 months... just back into it... thanks again...
Happy New Year!

John Spencer (MVP) said:
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather said:
Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
Click to expand...
Click to expand...
 
Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather


John Spencer (MVP) said:
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather said:
Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
Click to expand...
Click to expand...
 
I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Heather said:
Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

John Spencer (MVP) said:
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather said:
Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much John.... I can't tell you how much I appreciate all your help

John Spencer (MVP) said:
I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Heather said:
Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

John Spencer (MVP) said:
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather Polasek wrote:

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much John... I really appreciate it!!!

John Spencer (MVP) said:
I'm guessing you want to have NULL returned for the entire expression.
In VBA:
IIF (IsNull([Fair Market Value Offer]),Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

In SQL the following _MIGHT_ be a little more efficient, but probably not
noticeable in most situations.
IIF ([Fair Market Value Offer] Is Null,Null,"FMV" & Format([Fair Market Value
Offer], "currency") & " on " & [FMV Offer Dt]))

Heather said:
Hey John ~

I have another question, how would you add IIF(Is null) to this expression
(If Fair Market Value Offer field is empty then I want it to be null)

FMV: "FMV" & Format([Fair Market Value Offer], "currency") & " on " & [FMV
Offer Dt])

Heather

John Spencer (MVP) said:
You will need to use the Format function to do this.

Format([YourCurrencyField],"Currency") & " on " & Format([YourDateField],"mm/dd/yyyy")

Heather Polasek wrote:

Hey everyone ~

I would appreciate all the help I can get on this.... when I put a currency
field with a date field I loose my currency format but my dates format is ok.
ie $250,000.00 on 1/1/2005 but it shows up 250000 on 1/1/2005. I loose my
$, and decimal how come what am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top