Query calling function problem

[Ally]

Hi

Not been around for a while but good to be back!! I find myself using Access again which is great, but what appalling coding skills I did have, have kind of disappeared more or less completely apart from the dead simple stuff.

I have a query, where there are a few fields which are looking at the times between 2 dates. So I have done a DateDiff which is fine. Then I have to find the minimum of those fields to put in a query. I originally tried

IIf([field1]<[field2],[field1],[field2] but for some reason it works in some records and not in others.

Having looked through the forum I found this.

http://support.microsoft.com/default...b;en-us;209857

So I have copied this into a module:
It is a Public Function

So in my query I now have:
Minimum([field1],[field2])

But ... I get the error message
Undefined function 'minimum' in expression

Can anyone tell me what I'm doing wrong please?!

Added: I tried this in Northwind as per the example and it's fine. Copied exactly the same table, query and code into my db and it doesn't work!?!?

Many thanks

Ally

 

[Brianwarnock]

You haven't also called the module minimum have you?

Brian

 

[Ally]

You haven't also called the module minimum have you?

Brian

No! modMinMax

And in the query I called the field, MinScanTm

 

[Brianwarnock]

Couldn't follow the link as the knowledge database currently unavailable.

I wonder why the original IIf didn't always work.

Brian

 

[Ally]

Couldn't follow the link as the knowledge database currently unavailable.

I wonder why the original IIf didn't always work.

Brian

Doh! Might because I put it in the code option rather than hyperlink:

Try this
http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[Brianwarnock]

I copied the link and got to microsoft support and got the message there but will try again.

Brian

 

[Brianwarnock]

Ok worksfor me on 2002 Sp3, but it returns a string or atleast the date left justified.

ah! this is to stop Nulls causing errors as it would if one field was Null, it could have checked of course as I would have.

Brian

 

[Ally]

Ok worksfor me on 2002 Sp3, but it returns a string or atleast the date left justified.

Brian

For me it works in Northwind, but not in my db. I'm on Acc 2003, SP3.

 

[Brianwarnock]

Sorry but I am not going to be able to help you ALLy.
Isn't this where people usually say compact and repair your DB and try again.

Hope somebody else comes on and helps.

BTW do you have any ideas as to why the simple comparison doesn't always work.?

Brian

 

[Ally]

Sorry but I am not going to be able to help you ALLy.
Isn't this where people usually say compact and repair your DB and try again.

Hope somebody else comes on and helps.

BTW do you have any ideas as to why the simple comparison doesn't always work.?

Brian

Well that's really weird - thank you! I only did this earlier so didn't think I'd need to but the function is now {sort of} working! But unfortunately it's giving the same results as the comparison! So now I have another problem.

ie, some records are okay, but others, there is for example in one field 93 and another field 10256, but it's returning the 10256 as the "minimum".

These are the results of a DateDiff, so they should be normal, bog-standard numeric I thought?!?!

Aagh!

 

[Brianwarnock]

For some reason I thought we were talking about date fields ! the fields being compared aren't text fields are they?

Brian

 

[Ally]

For some reason I thought we were talking about date fields ! the fields being compared aren't text fields are they?

Brian

Well, they're they result of an IIf with a DateDiff. So some fields are numeric, and some are text, where I've asked it to reutrn "No Date" if there are no dates in any of the required fields.

Here's the IIf:

Tm_1stContTo1stScan: IIf(IsNull([dttm_1stcontact]),"No 1st contact",IIf(IsNull([dttm_1stscan]),"No date",DateDiff("n",[dttm_1stcontact],[dttm_1stscan])))

There two of these, very similar, just looking up a different field.

 

[MSAccessRookie]

Well, they're they result of an IIf with a DateDiff. So some fields are numeric, and some are text, where I've asked it to reutrn "No Date" if there are no dates in any of the required fields.

Here's the IIf:

Tm_1stContTo1stScan: IIf(IsNull([dttm_1stcontact]),"No 1st contact",IIf(IsNull([dttm_1stscan]),"No date",DateDiff("n",[dttm_1stcontact],[dttm_1stscan])))

There two of these, very similar, just looking up a different field.

As Brian Pointed out, When you compare 93 to 10256, you will get different results if the Fields are Strings versus Numbers. While the Number 93 is less than the Number 10256, the String "93" is greated than the String "10256".

 

[Brianwarnock]

As some of the content can be text then Tm_1stContTo1stScan will default to a string and thus a text comparison will take place.

Brian

 

[Ally]

Sorry - I'm just a bit confused now!!

Are both the DateDiff fields returning strings regardless of whether they return a string in one field and a numeric in another?

Both fields are exactly the same. The code is exactly the same bar one date field.

Any ideas how I can get around this please?

 

[Ally]

Ah - (sorry posted mine before I'd realised you'd posted Brian).

So if I take out the text from these DDiff fields, my problem should be solved! (?)

 

[Brianwarnock]

Yes, for now, but I guess you want to use that info, can you not use say 2 negative numbers which will be less than the datediff reurn and then convert those to the text info for your report or whatever.

Just a thought.

Brian

 

[raskew]

Hi -

Here is the function, copied from http://support.microsoft.com/kb/209857

Function Minimum(ParamArray FieldArray() As Variant)
   ' Source: http://support.microsoft.com/kb/209857
   ' Declare the two local variables.
   Dim I As Integer
   Dim currentVal As Variant

   ' Set the variable currentVal equal to the array of values.
   currentVal = FieldArray(0)

   ' Cycle through each value from the row to find the smallest.
   For I = 0 To UBound(FieldArray)
      If FieldArray(I) < currentVal Then
         currentVal = FieldArray(I)
      End If
   Next I

   ' Return the minimum value found.
   Minimum = currentVal

End Function

As pointed out, if you're inputting strings (vs numbers), you'll end up with unwanted results. One way to combat this is to surround your input with the val() function. Examples:

x = "10533"
y = "96"

? minimum(x,y)
10533

? minimum(val(x),val)
96

HTH - Bob

 

[Ally]

Yes, for now, but I guess you want to use that info, can you not use say 2 negative numbers which will be less than the datediff reurn and then convert those to the text info for your report or whatever.

Just a thought.

Brian

That's fine - I can pull it out via another way.

Not sure what you mean about using 2 negative numbers. Using negative numbers might be okay, but my next step is to filter those out, because where there's a negative number, there's an error in the data that was entered and I'm not using that to report on.

Eg, if I have a return of -2100 in one field and a return of 125 in another, I actually am going to need to use the 125. (Ultimately I'm trying to find out whether a scan was performed within 24 hours of the patient's first contact. I have had to break it down by minutes, because the date fields are the date and time and some are just date, so using anything higher like hours or days caused errant data. So in the end I'm going to be pulling out those that return between 0 and 1440 (1440 minutes in a 24 hour period).

Thank you for your help!

 

[Ally]

Hi -

As pointed out, if you're inputting strings (vs numbers), you'll end up with unwanted results. One way to combat this is to surround your input with the val() function. Examples:

x = "10533"
y = "96"

? minimum(x,y)
10533

? minimum(val(x),val)
96

HTH - Bob

Ooo thanks Bob. I'll try that.