Date field - programming

  • Thread starter Thread starter Liat
  • Start date Start date
L

Liat

I have an access project that contains 1 main table which
is filled by a form.
One kind of field is "date" field, which is doing me a
problem.

I have a report that should show the last date. for that
I wrote a function that gets 3 field of date and returns
the last one of them. I built a query that her fields are
the results of the function - this is related to the
report

The problem is that if one of those fields is empty, he
doesn't get into the function.

Trying to resolve this problem, I initialized the text
fields with #09/09/9999# and if the value of a field is
that initialized value, then I change the font color to
white (so it will look like empty).
The problem that this doesn't solve is - if a user starts
to write and then deletes, so again, I have an empty
value in the field. I tried to use the "BeforeUpdate"
function in order to put the #09/09/9999# value in case
the field is empty, but it brings an error, saying that
this is not a valid value.

Thanks a kot,
Liat
 
1. Are you talking a bout an Access Data Project (an ADP file with MS-SQL
Server back-end) or a MDB file?

2. Post relevant details of your Tables.

3. Post the SQL String of your Query / View.

4. Post the code of your UDF (User-Defined function).
 
1. I am talking about a MDB file.
2. For this matter, I have one main table
called "tviot".
It has identical details and 3 fields of estimation
dates, 3 fields of estimation values.
3. In the fields of the query, I call the function
GetLastEstimation([tviot]![estimation1], [tviot]!
[estimation2], [tviot]![estimation3], [tviot]!
[estimation1Date], [tviot]![estimation2 Date], [tviot]!
[estimation3 Date], [Forms]![F_Menu]![From_Date],[Forms]!
[F_Menu]![To_Date],[Forms]![F_Menu]![DateV]). This
function returns the last estimation. (for example, if
the date of estimation2 is empty then return estimation1)
The reason I send all those fields as parameters is that
I didn't succeed to get their values by code.
My problem is that if one of those fields is empty, the
function is not being called.
I tried to resolve this problem by putting a default
value in those fields.
But that caused other problem - if someone regrets and
deletes the date he entered, then this field becomes
empty and we return to the original problem.
In the function "beforeUpdate" I tried to check whether
the field is empty and if so to put the default value but
when I ran this' an error message appeared that the value
is not standing in the rules.


Thanks a lot,
Liat
 
1. It sounds like your UDF GetLastEstimation was not built correctly to
handle Null values. You probably need to use the Nz() function or to build
in some code to check for Null values and respond appropriately.

If you need more help to modify the function, post the function code
(requested in my earlier reply).

2. You Table is NOT normalised. Check your Table structure and ensure that
the Structure meets the requirements of the Relational Database Design
Principles. In fact, the Query is more complex because your Table is not
normalised.
 
My GetLastEstimation is GetLastHaaraha (haaraha =
estimation). Here is the code:

Function GetLastHaaraha(haaraha1 As Double, haaraha2 As
Double, haaraha3 As Double, currentDate1 As String,
currentDate2 As String, currentDate3 As String, fromD As
Date, toD As Date, checkDate As Integer)
If haaraha3 = 0 Then
If haaraha2 = 0 Then
If (checkDate = 0) Or (checkDate = -1 And
currentDate1 >= fromD And currentDate1 <= toD) Then
GetLastHaaraha = haaraha1
Else
GetLastHaaraha = 0
End If
Else
If (checkDate = 0) Or (checkDate = -1 And
currentDate2 >= fromD And currentDate2 <= toD) Then
GetLastHaaraha = haaraha2
Else
GetLastHaaraha = 0
End If
End If
Else
If (checkDate = 0) Or (checkDate = -1 And
currentDate3 >= fromD And currentDate3 <= toD) Then
GetLastHaaraha = haaraha3
Else
GetLastHaaraha = 0
End If
End If
End Function
If I call this function and one of the date's fields is
empty, I get an error. I put a debugger dot in this
function, called it and saw that if one of those fiels is
empty, we don't even enter the function.

I used the Nz in the parameters of dates and it solved
the problem. Thank you.
But is there any other way to do this?
What do you mean by not normalised?


-----Original Message-----
1. It sounds like your UDF GetLastEstimation was not built correctly to
handle Null values. You probably need to use the Nz() function or to build
in some code to check for Null values and respond appropriately.

If you need more help to modify the function, post the function code
(requested in my earlier reply).

2. You Table is NOT normalised. Check your Table structure and ensure that
the Structure meets the requirements of the Relational Database Design
Principles. In fact, the Query is more complex because your Table is not
normalised.

--
HTH
Van T. Dinh
MVP (Access)



1. I am talking about a MDB file.
2. For this matter, I have one main table
called "tviot".
It has identical details and 3 fields of estimation
dates, 3 fields of estimation values.
3. In the fields of the query, I call the function
GetLastEstimation([tviot]![estimation1], [tviot]!
[estimation2], [tviot]![estimation3], [tviot]!
[estimation1Date], [tviot]![estimation2 Date], [tviot]!
[estimation3 Date], [Forms]![F_Menu]![From_Date], [Forms]!
[F_Menu]![To_Date],[Forms]![F_Menu]![DateV]). This
function returns the last estimation. (for example, if
the date of estimation2 is empty then return estimation1)
The reason I send all those fields as parameters is that
I didn't succeed to get their values by code.
My problem is that if one of those fields is empty, the
function is not being called.
I tried to resolve this problem by putting a default
value in those fields.
But that caused other problem - if someone regrets and
deletes the date he entered, then this field becomes
empty and we return to the original problem.
In the function "beforeUpdate" I tried to check whether
the field is empty and if so to put the default value but
when I ran this' an error message appeared that the value
is not standing in the rules.


Thanks a lot,
Liat
Click to expand...



.
Click to expand...
 
The only data type that can accept Nulls is a Variant. For any of the
parameters in your function that might be Nulls, change the data type to
Variant. Inside the function, use IsNull to determine when the passed value
is null, so that you can handle it appropriately.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



My GetLastEstimation is GetLastHaaraha (haaraha =
estimation). Here is the code:

Function GetLastHaaraha(haaraha1 As Double, haaraha2 As
Double, haaraha3 As Double, currentDate1 As String,
currentDate2 As String, currentDate3 As String, fromD As
Date, toD As Date, checkDate As Integer)
If haaraha3 = 0 Then
If haaraha2 = 0 Then
If (checkDate = 0) Or (checkDate = -1 And
currentDate1 >= fromD And currentDate1 <= toD) Then
GetLastHaaraha = haaraha1
Else
GetLastHaaraha = 0
End If
Else
If (checkDate = 0) Or (checkDate = -1 And
currentDate2 >= fromD And currentDate2 <= toD) Then
GetLastHaaraha = haaraha2
Else
GetLastHaaraha = 0
End If
End If
Else
If (checkDate = 0) Or (checkDate = -1 And
currentDate3 >= fromD And currentDate3 <= toD) Then
GetLastHaaraha = haaraha3
Else
GetLastHaaraha = 0
End If
End If
End Function
If I call this function and one of the date's fields is
empty, I get an error. I put a debugger dot in this
function, called it and saw that if one of those fiels is
empty, we don't even enter the function.

I used the Nz in the parameters of dates and it solved
the problem. Thank you.
But is there any other way to do this?
What do you mean by not normalised?


-----Original Message-----
1. It sounds like your UDF GetLastEstimation was not built correctly to
handle Null values. You probably need to use the Nz() function or to build
in some code to check for Null values and respond appropriately.

If you need more help to modify the function, post the function code
(requested in my earlier reply).

2. You Table is NOT normalised. Check your Table structure and ensure that
the Structure meets the requirements of the Relational Database Design
Principles. In fact, the Query is more complex because your Table is not
normalised.

--
HTH
Van T. Dinh
MVP (Access)



1. I am talking about a MDB file.
2. For this matter, I have one main table
called "tviot".
It has identical details and 3 fields of estimation
dates, 3 fields of estimation values.
3. In the fields of the query, I call the function
GetLastEstimation([tviot]![estimation1], [tviot]!
[estimation2], [tviot]![estimation3], [tviot]!
[estimation1Date], [tviot]![estimation2 Date], [tviot]!
[estimation3 Date], [Forms]![F_Menu]![From_Date], [Forms]!
[F_Menu]![To_Date],[Forms]![F_Menu]![DateV]). This
function returns the last estimation. (for example, if
the date of estimation2 is empty then return estimation1)
The reason I send all those fields as parameters is that
I didn't succeed to get their values by code.
My problem is that if one of those fields is empty, the
function is not being called.
I tried to resolve this problem by putting a default
value in those fields.
But that caused other problem - if someone regrets and
deletes the date he entered, then this field becomes
empty and we return to the original problem.
In the function "beforeUpdate" I tried to check whether
the field is empty and if so to put the default value but
when I ran this' an error message appeared that the value
is not standing in the rules.


Thanks a lot,
Liat
Click to expand...



.
Click to expand...
Click to expand...
 
Back
Top