473,218 Members | 1,310 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

Filtering Queries that use a fucntion to return a value (Null Values in a Date Datatype)

Ken
I wrote a function to use in queries that takes a date and adds or
subtracts a certain length time and then returns the new value. There
are times when my function needs to return Null values.

Function DateCalc (blah...) As Variant
Do Stuff...
If Not IsNull(varNewDate) Then
DateCalc = varNewDate
End If
End Function

Then in a query - MyDate: DateCalc([Blah], [Blah], [Blah])

The results of this are in MyDate are left justified dates with a few
blank rows where the function returns a null value.

I can't filter the results on "Not Is Null", so I changed things a
bit:

MyDate: Nz(DateCalc([Blah], [Blah], [Blah]), "")

And using the criterion <> "". This seems like it will work, (I can
see the rows get populated and there are no rows with blanks), but at
the end there is an error message: Date type mismatch in criterion
expression. Then all of the cells in the results are filled with
"#Name?".

I even tried CStr(Nz(DateCalc([Blah], [Blah], [Blah]), "")), but got
the same results.

So next I changed the datatype of the funtion to Date, specified that
all the rows that would have returnd Null return "08/08/1888" (not
optimal, I know), and then running the query.

MyDate: DateCalc([Blah], [Blah], [Blah])
<>#08/08/1888#

It also looked like it would run fine, but at the end I got the same
error message about a data type mismatch in the criterion expression.

Finally, I tried querying my first query and using criterion in the
second query instead of using a criterion in my user defined function
field, but that also gave the data type mismatch error.

I don't know if it matters, but the MDB files reside on a Samba server
network.

What principles about functions and using them in queries and dealing
with Null values do I need to understand to overcome this problem?
Any suggestions?

Thanks!
Nov 12 '05 #1
1 2431
== Reply at bottom ==

Ken wrote:
I wrote a function to use in queries that takes a date and adds or
subtracts a certain length time and then returns the new value. There
are times when my function needs to return Null values.

Function DateCalc (blah...) As Variant
Do Stuff...
If Not IsNull(varNewDate) Then
DateCalc = varNewDate
End If
End Function

Then in a query - MyDate: DateCalc([Blah], [Blah], [Blah])

The results of this are in MyDate are left justified dates with a few
blank rows where the function returns a null value.

I can't filter the results on "Not Is Null", so I changed things a
bit:

MyDate: Nz(DateCalc([Blah], [Blah], [Blah]), "")

And using the criterion <> "". This seems like it will work, (I can
see the rows get populated and there are no rows with blanks), but at
the end there is an error message: Date type mismatch in criterion
expression. Then all of the cells in the results are filled with
"#Name?".

I even tried CStr(Nz(DateCalc([Blah], [Blah], [Blah]), "")), but got
the same results.

So next I changed the datatype of the funtion to Date, specified that
all the rows that would have returnd Null return "08/08/1888" (not
optimal, I know), and then running the query.

MyDate: DateCalc([Blah], [Blah], [Blah])
<>#08/08/1888#

It also looked like it would run fine, but at the end I got the same
error message about a data type mismatch in the criterion expression.

Finally, I tried querying my first query and using criterion in the
second query instead of using a criterion in my user defined function
field, but that also gave the data type mismatch error.

I don't know if it matters, but the MDB files reside on a Samba server
network.

What principles about functions and using them in queries and dealing
with Null values do I need to understand to overcome this problem?
Any suggestions?


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I ran across a similar problem today (same error, but different set up).

I had one query that used DateAdd() function to increment a Date column
value by 30 days. Sometimes the date value was NULL. Running query 1 -
everything came out as expected: Null Date columns return null results
& valid dates were incremented correctly.

But, when I incorporated query 1 into query 2 - when a NULL Date value
was processed by the DateAdd() & passed to query 2 the type mismatch
error occurred.

I finally used DateColumn + 30 instead of DateAdd("d", 30, DateColumn).
The error disappeared & query 2 worked as expected.

I figured NULL was somehow discombobulating the DateAdd() function, and
I knew that a NULL + integer would result in NULL, which is what I
wanted passed to query 2.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHSzoYechKqOuFEgEQJNOACdFubY0lhbwv4eD5LVNHnBGf +1T/cAnR1/
SiYxk6bEyGRD2XyzybrQoHWD
=lK/o
-----END PGP SIGNATURE-----

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nick Truscott | last post by:
Is this possible? I have a form which asks the user to input the result of a soccer match. I want to then use a choice of three sets of queries (Home Win, Away Win, Draw) to update two...
0
by: Skip Montanaro | last post by:
Consider the output of these two explain statements: mysql> explain select count(*) from cities,addresses,venues,events where cities.latitude <= 30.2741903768 and cities.latitude >=...
1
by: Alex Satrapa | last post by:
I have a table from which I'm trying to extract certain information. For historical reasons, we archive every action on a particular thing ('thing' is identified, funnily enough, by 'id'). So the...
13
by: Anton.Nikiforov | last post by:
Hello everybody! Does someone know how to build hierarchical queries to the postgresql? I have a table with tree in it (id, parent) and need to find a way from any point of the tree to any...
1
by: Ken | last post by:
I wrote a function to use in queries that takes a date and adds or subtracts a certain length time and then returns the new value. There are times when my function needs to return Null values. ...
4
by: John Smith | last post by:
Isn't life a bitch! You know what you want but you don't know how to get it. I have produced 12 queries that calculate a payment profile over 12 months. For a number of the records (ie with...
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
3
by: Liam Mac | last post by:
Hi All, Can anyone direct me or provide advice on how I can assign a null value to a date variable in vb.net. Basically what I'm doing is that I'm looping through a recordset where I have three...
2
Spazasaurus
by: Spazasaurus | last post by:
I am having trouble. I am not sure if it is not possible or not, but don't know any alternatives. I am converting my site from PHP and MYSQL to ASP.NET and MSSQL. In my current site. I did a query...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.