Select Query With Concatanate

[millertimecu32]

Hi all, I am trying to create a field that has a combination of two things in it. The first is a field that contains values the second is that if the field is null i wish to add the number 999,999. This is not a problem i can combine the function however it will not keep the formatting as a number. Has anyone delt with this before?

 

[KenHigg]

By def if you concat. you are converting the data to text. Either convert it back or use nz() to replace the nulls and leave the data numeric...



ken

 

[millertimecu32]

nz() I am not following you. This is what the language looks like. RBC.[RBC Soil Industrial mg/kg] & IIf((RBC.[RBC Soil Industrial mg/kg]) Is Null,"" & "999999") AS RBC_In_Soil

 

[KenHigg]

Try this:

RBC.[RBC Soil Industrial mg/kg] & nz(RBC.[RBC Soil Industrial mg/kg],"","999999")

(fyi: "RBC Soil Industrial mg/kg" is a real bad field name from a database perspective)


ken

 

[millertimecu32]

Where and how should i be entering this in? As you can tell i am a rookie at this stuff.

 

[KenHigg]

Sorry for the confusion. Are you doing this in the query builder?

 

[millertimecu32]

I tried this in the expression builder and still returns the field as text.

RBC: Nz([RBC Soil Industrial mg/kg],'999999')

 

[KenHigg]

I'm quessing you're trying to circumvent a divide by zero error...

There may be a better way overall, but for a quick stab I'd say simply wrap it in a cint() to convert it:

cint(Nz([RBC Soil Industrial mg/kg],'999999'))

or maybe:

Nz([RBC Soil Industrial mg/kg],999999)

???
ken

 

[millertimecu32]

Sorry I tried in the SQL view of the query builder and in the expression builder.

 

[KenHigg]

Can you put the two tables in a small database and post them and let me take look?

 

[millertimecu32]

I tried both the first returns the field in values but it places a #Error cint(Nz([RBC Soil Industrial mg/kg],'999999'))


The second returns the proper values but converts them to a text.
Nz([RBC Soil Industrial mg/kg],999999)

I really appreciate your help. This stuff really gets me flusterd sumtimes.

 

[KenHigg]

I'm still a little confused as to what you are trying to do. You have a field in table 'RBC' called 'RBC Soil Industrial mg/kg'. In the table is the field defined as a text or a numeric?

 

[millertimecu32]

In the table it is defined as numeric.

 

[millertimecu32]

I think i got it. I tried this and seems to have worked
RBC: CDbl(Nz([RBC Soil Industrial mg/kg],'999999'))

I really appreciate the help.

 

[KenHigg]

Cool - Glad you got it working.


ken