Informex vs SQL date problems

[jgs]

I have recently switched a large database from Informex to Windows SQL and am trying to get all my queries and reports to run correctly.



I have a Query that works with dates:

ptdate: IIf([pickprtdt]=1/1/1980,[trandte],[pickprtdt])

In Informex somehow I had the output formatted as yyyymmdd. In SQL it comes out ad yyyy-mm-dd

I tried:

format(IIf([pickprtdt]=1/1/1980,[trandte],[pickprtdt]),yyyymmdd) and it would not run and it wants to put yyyymmdd in brackets [] - plus I didn't have to use the format command with Informex.

I also tried entering yyyymmdd in the format section of the properties box

I also tied to format the output in the report I run from the query - it won't let me format it

Any suggestions?

[pbaldy]

I'm surprised it works at all without delimiters around the date. The format needs quotes around it. Try

format(IIf([pickprtdt]=#1/1/1980#,[trandte],[pickprtdt]),"yyyymmdd")

[jgs]

Thanks. I had delimiters and took them out when I was playing around with it. I was missing the "" - sometimes it takes another set of eyes.

I have another problem - I am trying to run this query:

qty: Abs(IIf([qtyshp]>0,[qtyshp],[altqtyshp]))

If quantity ship is greater than 0 (zero) - I want it to return that number. If it is 0 or less I want the alternate quantity ship - and I want absolute value with no minus signs.

I get the Error#

The table field would have .0000000 as an entry

It ran fine in Informex

Thoughts?

Thanks.

[pbaldy]

That looks okay offhand. You get the error on every line? That would imply a field name is spelled wrong or something. Or maybe the data type of the field being tested is text rather than numeric?

[jgs]

Yes it shows on Every Line

Checked data type in the data base shows as "short text"

It's not my database - and I can't change the data type. Is there a workaround for this?

[pbaldy]

Try

qty: Abs(IIf(CDbl(Nz([qtyshp], 0))>0,[qtyshp],[altqtyshp]))

The Nz() function makes sure the value isn't Null, which would cause an error in the CDbl() function.

[jgs]

That worked. Thank you.

I am familiar with NZ and its uses.

I do not know CDbl - and after a quick search - I'm not sure I understand it - but I will figure it out.

Thank you for our help. Saved me a lot of time banging my head against the keyboard.

Jeb

[pbaldy]

Happy to help. There are a number of conversion functions, each to a different data type: CInt() for Integer, CCur() for Currency, etc. I'd guess their most common use is similar to this, to take a text value and make it a numeric value.