Referring to a Calculated Field in a Recordset

[virtualpete]

Hi, I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.

my record set is

Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)

The calculated filed I have added in is Round(Price/7,2) AS Daily_rate

If I let it run just with this it runs fine.

and then I try to use this calculated field:

Do Until rstPrice.EOF

Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop

rstPrice.Close ' Tidy up
Set rstPrice = Nothing

They it stops running and it all seems to be because of the Daily_rate field

Can anyone shed any light as to what I maybe doing wrong.

 

[TJPoorman]

They it stops running and it all seems to be because of the Daily_rate field

What do you mean? What error is it giving? What is happening?

 

[virtualpete]

Thanks TJP, I do not get any messages it just hangs. If I remove the Daily_rate field it works fine and outputs a file as it should.

Is there a way to work through the script to see what it does not like?

 

[TJPoorman]

you can set breakpoint by clicking on the left of the code (in the grey bar). It will add a red circle to indicate the point it will stop.

Then when you fire the code it will stop on that line and you can step through by pressing F5 to continue once you have inspected the elements.

 

[virtualpete]

Hi TJP, thanks for the advice. Well I think I have found a possible cause.

The field Price from which I am trying to apply the calculation is actually a Text field. Now the strange thing is that it works on all values that have a £ sign but it faulters on those that have a € sign. I can of course change these values in the database but it is quite involved.

Do you know of a way to perhaps ignore currency sign I have in the table so it would work on euros and not only on GBP ?

 

[TJPoorman]

if the symbol is in the same position in both instances (first character), you could do this:

Round(Val(Mid([Price], 2))/7, 2) AS Daily_rate

This takes midway through the string starting at the second character and converts that to a number using VAL()

 

[virtualpete]

Thanks TJP, I will give that a go.

Thanks for your help.

 

[virtualpete]

Hi TJP, I wonder if you can help with anotehr small issue which I am sure there is an easy solution.

Where is am using the Round(Price/7,2) AS Daily_rate in the recordset is there a way to put a text string if a field is empty so I will end up with correct Daily_rate values plus values saying POA.

So if Field Price is empty of any value I would like to put POA (price on application).

I am sure there is a IF Null ..........


Many thanks if you can help

 

[TJPoorman]

Use the Nz() function. So like this:

Nz([Price],0)

This will convert NULL's to 0.

 

[virtualpete]

Hi TJP, thanks for that.

Do I place the function in the recordet like this

Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price,Nz([Price],0), Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)


I look forward to hearing from you.

 

[TJPoorman]

Like this:

Set rstPrice = db.OpenRecordset("SELECT Period_desc, Nz([Price],0) AS ZeroPrice, Round([COLOR="Red"]Nz([Price],0)[/COLOR]/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)

Be sure to use the Nz inside your calculation as well.

 

[virtualpete]

Thanks TJP

I apprecaite your help and time