Problems with Date variables in a function

  • Thread starter Thread starter arnold roth
  • Start date Start date
A

arnold roth

I'm attempting to calculate some dates in a database
query. The problem is to determine a date based on two
fields, a date and a string. In this case, the string is
a verification method and the date is the expected date on
which the method is employed.

I've written a simple function to calculate the dates as
follows:
Function Date_Calculate(test_type As String, date_1 As
Date) _
As Date
'Print test_type
'Print date_1
Select Case test_type
Case "Test"
Date_Calculate = date_1 + 30
Case "Demonstration"
Date_Calculate = date_1 + 20
Case "Inspection", "Examination"
Date_Calculate = date_1 + 10
Case "Analysis"
Date_Calculate = date_1 + 40
Case Else
Date_Calculate = date_1
MsgBox ("No Verification Method Specified."
& Chr(13) _
& "Please specify a method.")
End Select
End Function

The function is called from a query as follows:
Date_Calculate([Verification Methods]![NG ACC METH],CDate
([Verification Events]![Event_Date])).

In the database tables, NG ACC METH is a text field and
Event_Date is a short date field.

There are two problems:

1. The MsgBox code will not compile. Get an error message
about being out of the Type box (or something like that).
I also cannot add debug print statements inside the
function without getting compiler errors.

2. The function produces #err in every record when it is
run. The only way to make it work is to replace the date
input with a hard coded date as datevalue("string").

Can anyone tell me what I'm doing wrong, or better, how to
make it right?

Many thanks
 
arnold said:
MsgBox ("No Verification Method Specified."
& Chr(13) _
& "Please specify a method.")
End Select
Click to expand...
The function is called from a query as follows:
Date_Calculate([Verification Methods]![NG ACC METH],CDate
([Verification Events]![Event_Date])).
Click to expand...

In a query, I expect the dot operator (.) not the bang (!) to reference
fields
In the database tables, NG ACC METH is a text field and
Event_Date is a short date field.
Click to expand...

There is no need for CDate if the field is already date!
1. The MsgBox code will not compile. Get an error message
about being out of the Type box (or something like that).
I also cannot add debug print statements inside the
function without getting compiler errors.
Click to expand...

MsgBox, when run as a statement (as it does in your code) doesn't
require parentheses. What? You shouldn't use them.
2. The function produces #err in every record when it is
run. The only way to make it work is to replace the date
input with a hard coded date as datevalue("string").
Click to expand...

All right, but if you can calculate the value, why store it? This is not
liberal, I'll put it in the vernacular: don't store those values.
 
arnold said:
I'm attempting to calculate some dates in a database
query. The problem is to determine a date based on two
fields, a date and a string. In this case, the string is
a verification method and the date is the expected date on
which the method is employed.

I've written a simple function to calculate the dates as
follows:
Function Date_Calculate(test_type As String, date_1 As
Date) _
As Date
'Print test_type
'Print date_1
Select Case test_type
Case "Test"
Date_Calculate = date_1 + 30
Case "Demonstration"
Date_Calculate = date_1 + 20
Case "Inspection", "Examination"
Date_Calculate = date_1 + 10
Case "Analysis"
Date_Calculate = date_1 + 40
Case Else
Date_Calculate = date_1
MsgBox ("No Verification Method Specified."
& Chr(13) _
& "Please specify a method.")
End Select
End Function

The function is called from a query as follows:
Date_Calculate([Verification Methods]![NG ACC METH],CDate
([Verification Events]![Event_Date])).

In the database tables, NG ACC METH is a text field and
Event_Date is a short date field.

There are two problems:

1. The MsgBox code will not compile. Get an error message
about being out of the Type box (or something like that).
I also cannot add debug print statements inside the
function without getting compiler errors.
Click to expand...

I think you have to remove the ( ) in the MsgBox. Also,
Chr(13) is not adequate, either use the VBA constant vbCrLf
*or vbNewLine) or both CHr*13) & Chr(10).
2. The function produces #err in every record when it is
run. The only way to make it work is to replace the date
input with a hard coded date as datevalue("string").
Click to expand...


Your field references have to use dot, not bang.
 
Marsh,

Thanks for your quick reply. I have removed the
parentheses from the MsgBox statement and changed the
carriage return and that compiles now. However, changing
the !s to .s made no difference in running the query. I
still get #err on every record. Do you have any other
thoughts?

By the way, this is my first attempt at using VBA in
Access, so please forgive the ignorance but on the MsgBox
statement, I slavishly followed the syntax that popped up
as I typed. Why is that wrong?

Thanks again for your help
-----Original Message-----
arnold said:
I'm attempting to calculate some dates in a database
query. The problem is to determine a date based on two
fields, a date and a string. In this case, the string is
a verification method and the date is the expected date on
which the method is employed.

I've written a simple function to calculate the dates as
follows:
Function Date_Calculate(test_type As String, date_1 As
Date) _
As Date
'Print test_type
'Print date_1
Select Case test_type
Case "Test"
Date_Calculate = date_1 + 30
Case "Demonstration"
Date_Calculate = date_1 + 20
Case "Inspection", "Examination"
Date_Calculate = date_1 + 10
Case "Analysis"
Date_Calculate = date_1 + 40
Case Else
Date_Calculate = date_1
MsgBox ("No Verification Method Specified."
& Chr(13) _
& "Please specify a method.")
End Select
End Function

The function is called from a query as follows:
Date_Calculate([Verification Methods]![NG ACC METH],CDate
([Verification Events]![Event_Date])).

In the database tables, NG ACC METH is a text field and
Event_Date is a short date field.

There are two problems:

1. The MsgBox code will not compile. Get an error message
about being out of the Type box (or something like that).
I also cannot add debug print statements inside the
function without getting compiler errors.
Click to expand...

I think you have to remove the ( ) in the MsgBox. Also,
Chr(13) is not adequate, either use the VBA constant vbCrLf
*or vbNewLine) or both CHr*13) & Chr(10).
2. The function produces #err in every record when it is
run. The only way to make it work is to replace the date
input with a hard coded date as datevalue("string").
Click to expand...


Your field references have to use dot, not bang.
Click to expand...
 
Thanks for your reply. I'm sorry, the Cdate was one of my
debugging tries and had no effect anyhow. Replacing the !
s with .s had no effect on the query.

I don't understand your comment about not storing the
values if I could calculate them. I was trying to
describe some of the debugging attempts to make the query
work. If I understand queries, the resulting data is not
stored but recalculated every time. The basis of the query
is that the input dates will change as the project
progresses and the inevitable delays creep in. I need the
input dates as the basic data.

-----Original Message-----
arnold said:
MsgBox ("No Verification Method Specified."
& Chr(13) _
& "Please specify a method.")
End Select
Click to expand...
The function is called from a query as follows:
Date_Calculate([Verification Methods]![NG ACC METH],CDate
([Verification Events]![Event_Date])).
Click to expand...

In a query, I expect the dot operator (.) not the bang (!) to reference
fields
In the database tables, NG ACC METH is a text field and
Event_Date is a short date field.
Click to expand...

There is no need for CDate if the field is already date!
1. The MsgBox code will not compile. Get an error message
about being out of the Type box (or something like that).
I also cannot add debug print statements inside the
function without getting compiler errors.
Click to expand...

MsgBox, when run as a statement (as it does in your code) doesn't
require parentheses. What? You shouldn't use them.
2. The function produces #err in every record when it is
run. The only way to make it work is to replace the date
input with a hard coded date as datevalue("string").
Click to expand...

All right, but if you can calculate the value, why store it? This is not
liberal, I'll put it in the vernacular: don't store those values.
--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

.
Click to expand...
 
Arnold said:
Thanks for your reply. I'm sorry, the Cdate was one of my
debugging tries and had no effect anyhow. Replacing the !
s with .s had no effect on the query.

I don't understand your comment about not storing the
values if I could calculate them. I was trying to
describe some of the debugging attempts to make the query
work. If I understand queries, the resulting data is not
stored but recalculated every time. The basis of the query
is that the input dates will change as the project
progresses and the inevitable delays creep in. I need the
input dates as the basic data.
Click to expand...

I see, my reaction was automated somewhere. Never mind that.

Would you care to post the complete query (SQL) so I can lazily test it?
 
Thanks for your help. My problem was in the relationship
between some of the tables. Your advice got me there.
Thanks again.
 
Back
Top