Error in Concatenating two values in MS Access Function

[FA]

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

 

[Michael J. Strickland]

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

Try it without the brackets


--

 

[SusanV]

Get rid of the brackets

 

[Klatuu]

It will need more than the brackets. Since Val2 is a data, it needs to be
Val1 & " " & Format(Val2, "???")
Where ??? is whatever format the date needs to be in.

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

Try it without the brackets


--

 

[SusanV]

Good point - I jumped on the obvious.

Nice Catch Klatuu!

=)

It will need more than the brackets. Since Val2 is a data, it needs to be
Val1 & " " & Format(Val2, "???")
Where ??? is whatever format the date needs to be in.

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

Try it without the brackets


--

 

[John Nurick]

The function you posted doesn't produce the compilation error you
report, so you must have been testing something different.

Maybe you mistyped the function name in the Immediate pane.

Here's a functioning version of what you posted:

Public Function CalcFindingNo(S As String, D As Date) As String
CalcFindingNo = S & " " & Format(D, "Short Date")
End Function

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

 

[RoyVidar]

FA wrote in message

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

Dim Val1 As String
Dim Val2 As Date
Dim Val3 As String

Val3 = [Val1] & " " & [Val2]

End Function

When i enter two values in the immediate window to test the funtion it
is giving me the error

Complie error:
Sub or Funtion not defined

What seems to be the problem????

Can someone help me out please

Moe

Get rid of the declaration within the function, as they are alredy
"declared" in the function declaration

Public Function CalcFindingNo(Val1 As String, Val2 As Date) As String

CalcFindingNo = Val1 & " " & format$(Val2, "dd\/mm\/yyyy")

End Function

Also - to test in in the immediate pane, I think for 2000 and later
versions, it will need to be placed in a standard module, not a forms/
reports class module

 

[John Vinson]

Complie error:
Sub or Funtion not defined

What seems to be the problem????

One possibility is if you have this function in a Module which is also
named CalcFindingNo. The names of the Modules, Subs, and Functions
must all be unique. Try changing the name of the module to
basCalcFindingNo (or any other non-duplicated name).

See my reply in your other thread as well - you don't need this
function to accomplish what you're trying to do.

John W. Vinson[MVP]

 

[FA]

Ok I have tried the following

Public Function CalcFindingNo(Val1 As String, Val2 As String) As String
CalcFindingNo = Val1 & " " & Format$(Val2, "dd\/mm\/yyyy")
End Function

and in the immediate window i type
ab, 01/01/2006

It should give me ab01/01/2006
but instead when i hit the enter it give me compile error
Sub or Funtion not defined.

Actually i made this funtion because i have a little form that has
three controls txtbox1, txtbox2, txtbox3
txtbox1 providing Val1 in String formate
txtbox2 prividing Val2 in Date formate

txtbox3 should show the concetenated result of values of txtbox1 and
txtbox2.
so in the Afterupdate event of txtbox 2 i have
Me.txtbox3 = CalcFindingNo(Me.txtbox1, Me.txtbox2)

It should fill the txtbox3 with the concetenated value.

when i do that it gives me the same error message that it gave me in
the immediate window when i enter two values.

Please help

Moe

 

[RoyVidar]

FA wrote in message

Ok I have tried the following

Public Function CalcFindingNo(Val1 As String, Val2 As String) As String
CalcFindingNo = Val1 & " " & Format$(Val2, "dd\/mm\/yyyy")
End Function

and in the immediate window i type
ab, 01/01/2006

It should give me ab01/01/2006
but instead when i hit the enter it give me compile error
Sub or Funtion not defined.

Actually i made this funtion because i have a little form that has
three controls txtbox1, txtbox2, txtbox3
txtbox1 providing Val1 in String formate
txtbox2 prividing Val2 in Date formate

txtbox3 should show the concetenated result of values of txtbox1 and
txtbox2.
so in the Afterupdate event of txtbox 2 i have
Me.txtbox3 = CalcFindingNo(Me.txtbox1, Me.txtbox2)

It should fill the txtbox3 with the concetenated value.

when i do that it gives me the same error message that it gave me in
the immediate window when i enter two values.

Please help

Moe

To get a result, you must start with what you wish to do, which is
probably to print. In the immediate pane that can be written as a
question mark, then the function call

?CalcFindingNo("ab", #01/01/2006#)

What does it give?

 

[Rob Oldfield]

Something entirely different is giving you the compile error. Go to a code
window and hit Debug, Compile.

 

[FA]

When i tried to copy paste the following in the immediate pan

?CalcFindingNo("ab", #01/01/2006#)

it gives me the following error:
Expected Variable or Proecedure, not Module

What do i do ?? Please someone help me

Thanks
Moe

 

[RoyVidar]

FA wrote in message

When i tried to copy paste the following in the immediate pan

?CalcFindingNo("ab", #01/01/2006#)

it gives me the following error:
Expected Variable or Proecedure, not Module

What do i do ?? Please someone help me

Thanks
Moe

The reason I asked this, was because I suspected John Vinson was
correct when he mentioned the naming of your module. You have probably
named the module the same as the function.

Rename the module to something else (often you'll find naming
conventions prefixing module names with either bas or mod).

 

[John Vinson]

Ok I have tried the following

Public Function CalcFindingNo(Val1 As String, Val2 As String) As String
CalcFindingNo = Val1 & " " & Format$(Val2, "dd\/mm\/yyyy")
End Function

and in the immediate window i type
ab, 01/01/2006

It should give me ab01/01/2006
but instead when i hit the enter it give me compile error
Sub or Funtion not defined.

That's because there is no function named ab.

If you want to call the function... call the function.

Did you see my other thread, explaining why you *do not need this
function at all*???

Looking downthread, it appears that you're ignoring both that and my
suggestion about renaming the module.

Please reread - and APPLY - the suggestions in this thread.

John W. Vinson[MVP]

 

[FA]

John i am very carefully reading your suggestions and you are the
expert and i alwasy would follow your advise. I have mentioned in my
other threads how important for me is to accomplish this functionality
and you have mentioned some suggestions and i implemented them but did
not work correctly for me, perhaps i am doing somthing wrong. But now
that i have implemented this function with the different name as the
module name, it worked for me just fine. I am just a bit far from what
i need to achieve.
I have the following Funtion into a module name CalcFindingNo

Public Function FindingNo(S As String, D As Date, RecNo As Long) As
String
FindingNo = S & Format$(D, "dd\/mm\/yyyy") & Format$(RecNo, "00000")

End Function

It is working fine for me without the RecNo but when i put the RecNo
and test this ?FindingNo("ab", #01/01/2006#) it gives me the compile
error: Argument not Optional.

I need to have the 001 generated at the end of each concetenated
FindingNo. so if i pass this ?FindingNo("ab", #01/01/2006#), i should
be able to see
ab01/01/2006001.

What i am doing wrong this time? can someone help me ?

Thanks Millions John and everybody else.

Moe

 

[RoyVidar]

FA wrote in message

John i am very carefully reading your suggestions and you are the
expert and i alwasy would follow your advise. I have mentioned in my
other threads how important for me is to accomplish this functionality
and you have mentioned some suggestions and i implemented them but did
not work correctly for me, perhaps i am doing somthing wrong. But now
that i have implemented this function with the different name as the
module name, it worked for me just fine. I am just a bit far from what
i need to achieve.
I have the following Funtion into a module name CalcFindingNo

Public Function FindingNo(S As String, D As Date, RecNo As Long) As
String
FindingNo = S & Format$(D, "dd\/mm\/yyyy") & Format$(RecNo, "00000")

End Function

It is working fine for me without the RecNo but when i put the RecNo
and test this ?FindingNo("ab", #01/01/2006#) it gives me the compile
error: Argument not Optional.

I need to have the 001 generated at the end of each concetenated
FindingNo. so if i pass this ?FindingNo("ab", #01/01/2006#), i should
be able to see
ab01/01/2006001.

What i am doing wrong this time? can someone help me ?

Thanks Millions John and everybody else.

Moe

Because you now have a function with three arguements - the string, the
date and now a number, and you send only 2 arguements, the string and
the date, it will complain about the missing arguement - the missing
number.

FindingNo("ab", #01/01/2006#, 42)

Though by the number format, you should excpect some more leading
digits.

 

[FA]

Thanks Roy, but what if i want to autogenerate those number. The number
should automatically populate starting from 001 for the first record
and 002 for the second and so on. Am i using the right approach?
Because i dont want to enter the number manually, i want to automate
it? if you have other approach to do so please let me know. Everything
else seems to be working fine execpt the last autonumber number.

 

[RoyVidar]

FA wrote in message

Thanks Roy, but what if i want to autogenerate those number. The number
should automatically populate starting from 001 for the first record
and 002 for the second and so on. Am i using the right approach?
Because i dont want to enter the number manually, i want to automate
it? if you have other approach to do so please let me know. Everything
else seems to be working fine execpt the last autonumber number.

Since you are saying you will start with 001 as the first number, 002
as
the next ... I must assume you are just starting this project.

Then I will very strongly urge you to take a read or two on relational
databases, as this is setup is far ideal being non atomic ...

Now - the rules of making relational databases are not there to kick
people in non mentionable parts of the anatomy, but to prevent
headaches and allow you as the responsible developer to not have to
stay
in the office outside business hours to clean up mess caused by this
....

Here's a nice article by Paul Litwin
http://r937.com/relational.html

I gather you're thinking of using this as a primary key?

Some say use only surrogate keys (autonumber/identity)
Some say use only natural keys

I say both are OK, I also use composite primary keys - but I never,
never, never concatenate values from different fields and stuff into
another field. Sooner or later this fields contents will be out of sync
with the fields it's derived from - and then you're out of luck ...

So I say - no - this is not the right approach for anything but future
headaches and urge you to rethink this approach ...

My point is - *do* *not* store this concatenated value - if it is
really
needed, then calculate it on the fly each time you need it for display
purposes. This is what I though this thread was about.

 

[FA]

You suggestion is 100% right Roy and i really appreciated that you took
your time to respond to me.
Field Name: FINDG_NO
Table Name: FINDG
Yes it is a primary key

Now the user do not want to enter the finding number manually. instead
they have standardized the finding number with System Code and
TestBeginDate and 001 for the first finding 002 for the second.
Each SystemCode would be unique and coming from table SYS_INFO that has
a one to many relationship with table FINDG.

I initially put this as a autonumber but the project managers want the
Finding Number to be composite of SYS_CODE and TestBeginDate (these two
fields are in Table SYS_INFO).
So i am taking these two fields from that table concetenating them and
saving them in FINDG_NO in table FINDG. Each FINDG_NO would be unique
since i will have 001, 002,003 at the end of each FINDG_NO.
Each SYS_CODE can have many FINDG_NO but SYS_CODE and Testbegindate
will remain the same only it will have different autonumbers at the
end.

I tried to convence my managers not to do it this way but they want to
have the FINDG_NO automated.

Is there anyother way beside concetenating those two fields and having
the autonumber at the end for each FINDG_NO??

If so please let me know because i am pulling my hairs at this point
;-((

Thanks Millions

Moe

 

[RoyVidar]

FA wrote in message

You suggestion is 100% right Roy and i really appreciated that you took
your time to respond to me.
Field Name: FINDG_NO
Table Name: FINDG
Yes it is a primary key

Now the user do not want to enter the finding number manually. instead
they have standardized the finding number with System Code and
TestBeginDate and 001 for the first finding 002 for the second.
Each SystemCode would be unique and coming from table SYS_INFO that has
a one to many relationship with table FINDG.

I initially put this as a autonumber but the project managers want the
Finding Number to be composite of SYS_CODE and TestBeginDate (these two
fields are in Table SYS_INFO).
So i am taking these two fields from that table concetenating them and
saving them in FINDG_NO in table FINDG. Each FINDG_NO would be unique
since i will have 001, 002,003 at the end of each FINDG_NO.
Each SYS_CODE can have many FINDG_NO but SYS_CODE and Testbegindate
will remain the same only it will have different autonumbers at the
end.

I tried to convence my managers not to do it this way but they want to
have the FINDG_NO automated.

Is there anyother way beside concetenating those two fields and having
the autonumber at the end for each FINDG_NO??

If so please let me know because i am pulling my hairs at this point
;-((

Thanks Millions

Moe

There is a mixup of terms here - composite - vs concatenated

Composite primary keys are a quite ordinary part of ordinary database
work - except if you're a complete surrogate key fan(atic) - but lot
of surrogate key fans, would not mind using composite primary keys
for instance in junction tables. But it is important to note that a
composite primary key consists of the actual fields, not a concatenated
mess. Again - a composite primary key is not a single field
concatenated
mess it is a primary key consisting of the original fields!

Fields that are concatenated from other fields are a complete no-no in
relational databases. If you have any control over the process at all
you simply don't do that, but concatenate whenever there's a need to
display it.

How to calculate the number, I do not know, if it should just start
from
1, why not just use an autonumber (and if so, why couldn't that be
used as primary key)? I'm sure there are lot of "Autonumber" code
solutions to be found through a search here.

So if this can be solved by autonumber, make the FINDG_NO field an
autonumber field, and stop this concatenation! Make a composite (not
concatenated primary key out of FINDG_NO, TestBeginDate and SYS_CODE -
but use those fields, not any concatenation scheme ...

- he he - you could have your managers read this, and see if it
helps;-)

Or check out NG's dedicated such issues, like for instance
microsoft.public.access.tablesdbdesign