External name not defined error

[Lifeseeker]

Hi there,

I have a table onto which i would like to add new column based on a set of if statements. (to assign weekly dates)

this is only a portion of the code, but when I clicked on run, it gives me "external name not defined error".

Option Compare Database
Option Explicit
 
Public Sub AssignWeekly()

'declares weekly column as a new variable
Dim Client_Weekly_1213 As Integer
 
'starts coding in weekly
If [P3].[intake date] >= #4/1/2012# And [P3].[intake date] < #4/8/2012# Then
Client_Weekly.Value = 1
End If
If [P3].[intake date] >= #4/8/2012# And [P3].[intake date] < #4/15/2012# Then
Client_Weekly.Value = 2
End If

can anybody assist? Is there anything that I did wrong?
I put the code in the module.

Thanks

 

[JHB]

What is [P3] And [intake date]? (You say you have put the code in a module, is it an external module or form module?
If external module then [P3] and [intake date] are unknown for MS-Access).
And what is Client_Weekly, (if it is a variable you have to declare it - Dim Client_Weekly as Integer)?

 

[Lifeseeker]

What is [P3] And [intake date]? (You say you have put the code in a module, is it an external module or form module?
If external module then [P3] and [intake date] are unknown for MS-Access).
And what is Client_Weekly, (if it is a variable you have to declare it - Dim Client_Weekly as Integer)?

Hi, [P3] is the name of the table and the [Intake date] is a field in the [P3] table.

This is not a form but a module. I went to VBA window and inserted a module and put the code in there. I could have put these codes in a query but there is apparently a limit to # of strings that Access can handle.

And yes, I should have put the client_weekly_1213 as opposed to client_weekly.

Any ideas?

 

[JHB]

[P3] is the name of the table and the [Intake date] is a field in the [P3] table.

They are unknown for MS-Access in the Module, you've to transfer the value of [P3].[intake date] to the procedure in the module as parameters.

 AssignWeekly(IntakeDate As Date)
   ..
  ..
  If IntakeDate >= #4/1/2012# And IntakeDate < #4/8/2012# Then
   ..

Do you want to call the procedure in the module from a query or how and where do you want to use it?

I could have put these codes in a query but there is apparently a limit to # of strings that Access can handle.

Hmm - how long is it then, (below is a link to Access 2010 specifications)?
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx

 

[Lifeseeker]

They are unknown for MS-Access in the Module, you've to transfer the value of [P3].[intake date] to the procedure in the module as parameters.

 AssignWeekly(IntakeDate As Date)
   ..
  ..
  If IntakeDate >= #4/1/2012# And IntakeDate < #4/8/2012# Then
   ..

Do you want to call the procedure in the module from a query or how and where do you want to use it?

Hmm - how long is it then, (below is a link to Access 2010 specifications)?
http://office.microsoft.com/en-us/access-help/access-2010-specifications-HA010341462.aspx

Hi,

this is what I have right now.

ption Compare Database
Option Explicit
public sub AssignWeekly([p3].[intake date] as Date)
'declares weekly column as a new variable
Dim Client_Weekly_1213 As Integer
'starts coding in weekly
If [p3].[intake date] >= #4/1/2012# And [p3].[intake date] < #4/8/2012# Then
Client_Weekly_1213.Value = 1
End If

I want to invoke it in a query later on. Right now, I have a highlight after "AssignWeekly" in public sub line. I'm just not too familiar with the syntax.

Any help is much appreciated.

 

[JHB]

Hi,

this is what I have right now.

option Compare Database
Option Explicit
public sub AssignWeekly([p3].[intake date] as Date)
'declares weekly column as a new variable
Dim Client_Weekly_1213 As Integer
'starts coding in weekly
If [p3].[intake date] >= #4/1/2012# And [p3].[intake date] < #4/8/2012# Then
Client_Weekly_1213.Value = 1
End If

I want to invoke it in a query later on. Right now, I have a highlight after "AssignWeekly" in public sub line. I'm just not too familiar with the syntax.

Any help is much appreciated.

Hi - it is still unknown for MS-Access, (you need to do it as I showed you in my former post.
When you call the procedure you "transfer" the value from [p3].[intake date] to the procedure like shown below, but it must be from a level where [p3].[intake date] is known by MS-Access.

Call AssignWeekly([p3].[intake date])

 

[Lifeseeker]

Hi, I am not sure what you mean by level. I think I get what you mean, but the code is not working.

I have attached a test copy of the database for you to see.

I have two questions:
1) where to put

Call AssignWeekly([cohort].[intake date])

2) how do I let Access know what cohort is?

Thanks

 

[JHB]

Oh yes I see - I only thought you had more experiences with programming as I saw you had >230 post, my fault.
I read you want to use it in a query, so I've made a small query to show how to use that function, (open and run the query "UseTheFunction").
You have to use "Function" instead of "Sub".