change format of field?

[thepunisher]

Hi im creating this query that makes a new field called 'Marginal Unit'.

I want to pick out a specific string in the field D_Data.

I can do this with other queries when i simply want to extract a numeric value, however i want to extract a string like 'David 1'.

When i use the same query to get these strings, the nely create field 'Marginal Unit' is filled with zeros.

I think i need to format this new field when i run the query, so how do i do this?

hope u can help,

'This is the query im using so far...but i think it can be changed in the query designer section....
----------------------------------------------------------
SELECT tblmrpt15x.A_Week_Beginning_Monday, tblmrpt15x.B_Hour_of_Week, tblmrpt15x.C_Row_No_Per_Hour, tblmrpt15x.D_Data, tblmrpt15x.E_Hour_Of_Day, tblmrpt15x.F_Actual_Date, IIf(Mid([D_Data],51,1)="x",Val(Mid([D_Data],26,9)),"") AS [Marginal Unit]
FROM tblmrpt15x
WHERE (((IIf(Mid([D_Data],51,1)="x",Val(Mid([D_Data],26,9)),""))<>""))
ORDER BY tblmrpt15x.A_Week_Beginning_Monday, tblmrpt15x.B_Hour_of_Week;
----------------------------------------------------------

kind regards,

thePunisher.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[PHV]

Have you tried to replace this :
IIf(Mid([D_Data],51,1)="x",Val(Mid([D_Data],26,9)),"") AS [Marginal Unit]
By this ?
IIf(Mid([D_Data],51,1)="x",Mid([D_Data],26,9),"") AS [Marginal Unit]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[wirelessben]

Get rid of the Val() function, which only returns the numbers in a string. The Mid function will give you alphanumeric data.

IIf(Mid([D_Data],51,1)="x",Mid([D_Data],26,9),"") AS [Marginal Unit]

Also get rid of the Val in the WHERE clause.