Type-sensitive parameterized views

[trimtrom]

Hello,

I am a Foxpro 6 beginner, and I have written a Foxpro report based on a parameterized view. This view compares the parameter to an alphanumeric field ACNO in its base table to select its records.

My problem is that the parameter the report passes to the view can be either in the form of a word (as in "PAUL") or a number (as in "5515"). I have both these examples stored in the field ACNO.

I find that the report will show beautifully when "PAUL" is used as the parameter, but flashes on and off (because it is empty) when the parameter is "5515". For some reason the view does not like to compare alphanumeric fields to a parameter where the parameter is a number (as far as I can see). I can find no other reason for this event.

How do I get round this?

Thanks,
Trimtrom

 

[Mike Gagnon]

Try adding ALLTRIM() in your parameter.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[trimtrom]

Hi,

Alltrim() makes no difference, I am afraid.

Trimtrom

 

[Mike Lewis]

Trimtrom,

Could you clarify what data types you are using here (for the field and the parameter), and what their lengths are? Aslo, what is the code in the WHERE clause of the view? It will be much easier to solve this problem given that information.

Mike




Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[trimtrom]

Hi,
In response to your question the where clause is as follows:

WHERE Exptran.trancc = ?paracostcentre;
AND Exptran.acno = ALLTRIM(?paraaccount)

paraaccount is the parameter in question and is of type character (I don't know the length), and acno is a 5 character length field.

Hope this helps toward a solution!

Trimtrom

 

[Olaf Doschke]

Hi trimtom,

I think it would work without ALLTRIM also.
If you query the same field, why should "PAUL"
work and "5515" not? Just beacuse there is
no record with "5515" for the same costcentre?

Maybe you have stored the numbers aligned right
and the text values left? then you have to
query for " 5515"?

Take a careful look at your data.

Bye, Olaf.

 

[Mike Lewis]

Trimtom,

I can't see any reason for your code not to work.

Your original idea was that it failed because 5515 is a numeric character string whereas PAUL isn't. I suggest that, as a next step, you check to see if that behaviour is consistent. Does it fail with any numeric string and succeed with any non-numeric string? What happens if with a paramter such as 55XX?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com