Agree with CJ; DB normalization would make this much easier for you. However, if that is a bridge too far, Arnelgp's function is your best bet. Access does not like complicated or nested IFF's. In my experience any way!
I'm assuming, now, that there's some sort of error or corruption in the referenced table because, I tested each of the suggested expressions in a brand new database, with a table with just the required fields for the query and entered some test records and the query worked fine.
I subsequently saved a copy of my original table, removed all fields that I didn't need for the query, then imported it to my "new" test database, ran the query and it didn't work.
I've attached it. Data table is the test table and this works fine with the query. However, when I amend the query to reference my table Doc1 it doesn't. The "Referral" fields have been renamed for simplicity for the example to R1, R2 etc.
I know I'm resurrecting this after a couple of weeks but, I'd like to know, if this module would/should also work with a date fields. I've tried calling it to check date fields but I receive a type mismatch error and the debugger gets "stuck".
I unfortunately wouldn't have a clue how to do that as I don't even know which of these declarations is referencing the string fields with my extremely limited knowledge of VBA. I basically want the module to check exactly the same number of fields, for the same reason, for the same result, but within date fields instead of string fields.
To quote an obscure movie: "You should not do magic you don't understand!"
Now that I got that out of my system, I feel your pain and I am glad you want to learn the capabilities of VBA to extend Access. However, without knowing your background I am going to guess that you still have a lot to learn in interactive Access.
Writing Functions and Subs can be challenging and it is forums like this that can help you learn, but for your own sake, don't rely solely on this site - there are a few books and tutorials that can help you so that the advice received here has a long-lasting benefit.
Now, regarding your situation, without knowledge of your DB/table structure it is impossible to give guidance. It appears that Arnelgp took your DB and wrote the function for you - which was very kind of him, but now, by your own admission, you do not know how he did it, what his code does and worse, how to alter it if it stops working due to changes in your objects (tables/forms/queries etc.).
These are things you are going to have to know and understand if you are going to maintain this application.
Now that I am through scolding you (!), my advice would be to ask Arnelgp to explain his code to you and how to alter it to include Date fields. It's not that I don't want to help, its just he has the most knowledge of what you're working with and quite frankly, his King-Fu is better than mine!
i slightly modified the function so it will accept Date, String and Numeric datatype.
you must specifically call the function with correct return type (first parameter).
you must modify all query's that depends on this function since, it is already modified:
you call it in query/or control in a form:
if your field holds string:
fnLastRef("String", field1, field2, field3, field4, ..., field(N))
if it is numeric field:
fnLastRef("Numeric", field1, field2, field3, field4, ..., field(N))
if it is date field:
fnLastRef("Date", field1, field2, field3, field4, ..., field(N))
for date field i replace our (/) delimiter, since it is used in date format, with chr(255). this is the last in the ascii code and i would say safe to use since who would embed an ascii 255 in a field anyway.
Code:
Public Function fnLastRef(DataType As String, ParamArray ref() As Variant) As Variant
'
' valid DataType:
' "String"
' "Date"
' "Numeric"
'
Dim v As Variant
Dim i As Integer
Dim pos As Long
For i = UBound(ref) To 0 Step -1
v = v & (IIf(Trim(ref(i) & "") = "", Null, ref(i) & "") + Chr(255))
Next
pos = InStr(v, Chr(255))
If (pos <> 0) Then
fnLastRef = Left(v, pos - 1)
Else
fnLastRef = ref(0)
End If
Select Case DataType
Case Is = "Numeric"
fnLastRef = Val(fnLastRef)
Case Is = "Date"
fnLastRef = CDate(fnLastRef)
End Select
End Function
i slightly modified the function so it will accept Date, String and Numeric datatype.
you must specifically call the function with correct return type (first parameter).
you must modify all query's that depends on this function since, it is already modified:
you call it in query/or control in a form:
if your field holds string:
fnLastRef("String", field1, field2, field3, field4, ..., field(N))
if it is numeric field:
fnLastRef("Numeric", field1, field2, field3, field4, ..., field(N))
if it is date field:
fnLastRef("Date", field1, field2, field3, field4, ..., field(N))
for date field i replace our (/) delimiter, since it is used in date format, with chr(255). this is the last in the ascii code and i would say safe to use since who would embed an ascii 255 in a field anyway.
Code:
Public Function fnLastRef(DataType As String, ParamArray ref() As Variant) As Variant
'
' valid DataType:
' "String"
' "Date"
' "Numeric"
'
Dim v As Variant
Dim i As Integer
Dim pos As Long
For i = UBound(ref) To 0 Step -1
v = v & (IIf(Trim(ref(i) & "") = "", Null, ref(i) & "") + Chr(255))
Next
pos = InStr(v, Chr(255))
If (pos <> 0) Then
fnLastRef = Left(v, pos - 1)
Else
fnLastRef = ref(0)
End If
Select Case DataType
Case Is = "Numeric"
fnLastRef = Val(fnLastRef)
Case Is = "Date"
fnLastRef = CDate(fnLastRef)
End Select
End Function
I've created this as a separate module and called it in a query using the date parameters from my form and it returns the correct result.
However, the format of the date appears to be "forced" into dd/mm/yyyy. I've entered my preferred format of d mmm yy in design view in the query and report but it still shows as dd/mm/yyyy. How can I change this?
