Problems with Date variables in a function

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
 
B

Bas Cost Budde

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.
 
M

Marshall Barton

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.
 
A

Arnold Roth

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...
 
A

Arnold Roth

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...
 
B

Bas Cost Budde

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?
 
A

Arnold Roth

Thanks for your help. My problem was in the relationship
between some of the tables. Your advice got me there.
Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF ([date_combo_box] Is Null,“xâ€, Between date 1 And date 2) 1
A string problem 6
Problems with calculation in a query 1
Controlling 'Date' entries in a form 2
Duplicating records in a form and subform (Allen Browne) 2
Excel Message box with days of month & date. 1
Help with my function vba code 1
Lapsed Time when 1 date is null 2

Top