Functions --> Expression

[Guest]

Good afternoon,
I have a few functions I have created that I would like to use to build a macro expression. Any help would be appreciated!

Public Function Quarteryear()
Quarteryear = InputBox("Enter the quarter and year you'd like reported (QQ-YYYY):")
End Function

Public Function Quarter()
Quarter = Mid$(Quarteryear, 2, 1)
End Function

Public Function Year()
Year = Right$(Quarteryear, 2)
End Function

First, are any of these reserved for VBA? If not, I can change them - not a problem. Finally, I have queries with names (bad names, I know!) of:

4Q-03 Labor Standards Updates
3Q-04 Labor Standards Updates

Obviously, my Quarteryear() InputBox should pull in format 03-2003. So, I want to use the Quarter and Year functions (to simplify the expression from mid$ and right$) to call the correct query. Here's what I have as my query expression:

=Quarter() & "Q-" & Year() & " Labor Standards Updates"

Yes, there is a space before Labor...

Thanks for the help in advance!
Derek

 

[TC]

there is no context (that I know of) where this:

=Quarter() & "Q-" & Year() & " Labor Standards Updates

would evaluate that expression (getting, for example, "4Q-03 Labor Standards
Updates") and then automatically run the query of that name.

Think about it! How then could anyone use the = syntax for its intended
purpose: evaluating an expression? An Access programmer expects ="XYZ" to
evaluate to the 3-letter string XYZ - not to run a query called XYZ!

If you are using VBA - not macros - you run a query using the OpenQuery
method (for example). You choose between alternatives using an IF or SELECT
statement. To see if something is a reserved word in Access or VBA, you look
up - waaaaaaaait for it - "reserved words" in online help.

HTH,
TC

Good afternoon,
I have a few functions I have created that I would like to use to build a

macro expression. Any help would be appreciated!

Public Function Quarteryear()
Quarteryear = InputBox("Enter the quarter and year you'd like reported (QQ-YYYY):")
End Function

Public Function Quarter()
Quarter = Mid$(Quarteryear, 2, 1)
End Function

Public Function Year()
Year = Right$(Quarteryear, 2)
End Function

First, are any of these reserved for VBA? If not, I can change them - not

a problem. Finally, I have queries with names (bad names, I know!) of:

4Q-03 Labor Standards Updates
3Q-04 Labor Standards Updates

Obviously, my Quarteryear() InputBox should pull in format 03-2003. So, I

want to use the Quarter and Year functions (to simplify the expression from
mid$ and right$) to call the correct query. Here's what I have as my query
expression:

 

[Cheryl Fischer]

Derek,

Access already has a built-in Year() function which returns a Variant
(integer) representing the 4-digit year. If only just for clarity and
perhaps to make it easier on someone who might work on your databases after
you go on to bigger 'n better things, you might consider renaming your
function to something like YearStr().

Also, here are links to the Reserved Words lists:

Access 1.0 through 97:
http://support.microsoft.com/default.aspx?scid=kb;en-us;109312

Access 2000:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187

Access 2002:
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Good afternoon,
I have a few functions I have created that I would like to use to build a

macro expression. Any help would be appreciated!

Public Function Quarteryear()
Quarteryear = InputBox("Enter the quarter and year you'd like reported (QQ-YYYY):")
End Function

Public Function Quarter()
Quarter = Mid$(Quarteryear, 2, 1)
End Function

Public Function Year()
Year = Right$(Quarteryear, 2)
End Function

First, are any of these reserved for VBA? If not, I can change them - not

a problem. Finally, I have queries with names (bad names, I know!) of:

4Q-03 Labor Standards Updates
3Q-04 Labor Standards Updates

Obviously, my Quarteryear() InputBox should pull in format 03-2003. So, I

want to use the Quarter and Year functions (to simplify the expression from
mid$ and right$) to call the correct query. Here's what I have as my query
expression:

 

[Guest]

Thanks, TC. Sorry, I left out the most important detail. OpenQuery action in a macro has that expression in the Query Name argument..

Someone previously helped me with a SelectGreeting for my SendObject action where the email message wa

=SelectGreeting() & "
This is to inform you that the attached contains the cancelled requests. Please call me to discuss. Thank you
Derek

SelectGreeting() is a salutation based on the time of day that the macro is run.

If I can use an expression in this type of argument, would I not be able to use it in the OpenQuery (query name) argument

Thanks again
Dere

----- TC wrote: ----

there is no context (that I know of) where this

=Quarter() & "Q-" & Year() & " Labor Standards Update

would evaluate that expression (getting, for example, "4Q-03 Labor Standard
Updates") and then automatically run the query of that name

Think about it! How then could anyone use the = syntax for its intende
purpose: evaluating an expression? An Access programmer expects ="XYZ" t
evaluate to the 3-letter string XYZ - not to run a query called XYZ

If you are using VBA - not macros - you run a query using the OpenQuer
method (for example). You choose between alternatives using an IF or SELEC
statement. To see if something is a reserved word in Access or VBA, you loo
up - waaaaaaaait for it - "reserved words" in online help

HTH
T

Good afternoon
I have a few functions I have created that I would like to use to build

macro expression. Any help would be appreciated

Quarteryear = InputBox("Enter the quarter and year you'd like reporte (QQ-YYYY):"
End Functio
Quarter = Mid$(Quarteryear, 2, 1
End Functio
Year = Right$(Quarteryear, 2
End Functio

want to use the Quarter and Year functions (to simplify the expression fro
mid$ and right$) to call the correct query. Here's what I have as my quer
expression

 

[Guest]

Cheryl
Thanks for pointing it out. Also thanks to you and TC for help finding the Reserved Words. I'll take your suggestion and change the name of my string.

Dere

----- Cheryl Fischer wrote: ----

Derek

Access already has a built-in Year() function which returns a Varian
(integer) representing the 4-digit year. If only just for clarity an
perhaps to make it easier on someone who might work on your databases afte
you go on to bigger 'n better things, you might consider renaming you
function to something like YearStr()

Also, here are links to the Reserved Words lists

Access 1.0 through 97
http://support.microsoft.com/default.aspx?scid=kb;en-us;10931

Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;20918

Access 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;28633

-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Good afternoon
I have a few functions I have created that I would like to use to build

macro expression. Any help would be appreciated

Quarteryear = InputBox("Enter the quarter and year you'd like reporte (QQ-YYYY):"
End Functio
Quarter = Mid$(Quarteryear, 2, 1
End Functio
Year = Right$(Quarteryear, 2
End Functio

want to use the Quarter and Year functions (to simplify the expression fro
mid$ and right$) to call the correct query. Here's what I have as my quer
expression

 

[Guest]

TC and Cheryl
I changed the Year() to YearStr() in the Public Function and in the expression and it worked! My only problem now is that it's prompting me for the InputBox twice. I thought that since I made them Public, it wouldn't do that

Forgot - I'm in 2000

Dere

----- TC wrote: ----

there is no context (that I know of) where this

=Quarter() & "Q-" & Year() & " Labor Standards Update

would evaluate that expression (getting, for example, "4Q-03 Labor Standard
Updates") and then automatically run the query of that name

Think about it! How then could anyone use the = syntax for its intende
purpose: evaluating an expression? An Access programmer expects ="XYZ" t
evaluate to the 3-letter string XYZ - not to run a query called XYZ

If you are using VBA - not macros - you run a query using the OpenQuer
method (for example). You choose between alternatives using an IF or SELEC
statement. To see if something is a reserved word in Access or VBA, you loo
up - waaaaaaaait for it - "reserved words" in online help

HTH
T

Good afternoon
I have a few functions I have created that I would like to use to build

macro expression. Any help would be appreciated

Quarteryear = InputBox("Enter the quarter and year you'd like reporte (QQ-YYYY):"
End Functio
Quarter = Mid$(Quarteryear, 2, 1
End Functio
Year = Right$(Quarteryear, 2
End Functio

want to use the Quarter and Year functions (to simplify the expression fro
mid$ and right$) to call the correct query. Here's what I have as my quer
expression

 

[Cheryl Fischer]

Hello Derek,

Actually, you could do all of this in one function call. Would this work?

Public Function Quarteryear() As String

Quarteryear = InputBox("Enter the quarter and year you'd like reported
(QQ-YYYY):")
Quarteryear = Mid$(Quarteryear, 2, 1) & "Q-" & Right$(Quarteryear, 2) & "
Labor Standards Updates"

End Function

Just seems neater to me...
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

TC and Cheryl,
I changed the Year() to YearStr() in the Public Function and in the

expression and it worked! My only problem now is that it's prompting me for
the InputBox twice. I thought that since I made them Public, it wouldn't do
that.

 

[TC]

Hi Derek

Sendbject is VBA (not a macro). Many parameters of VBA statement will accept
"expressions" as well as constants. SelectGreeting() sounds like the name of
a function. A function is one example of an expression. So SendObject would
probably accept SelectGreeting() in places where it would otherwise take a
constant instead.

Macros, in contrast, often do not have that flexibility. If a particular
macro parameter expects a constant - say, the name of a query - it will not
necessarily accept an expression instead - AFAIK, but I'm no expert on
macros!

Macros are a deprecated technology. It would be worth your while to start
moving away from them, & using VBA instead.

Cheers,
TC

Thanks, TC. Sorry, I left out the most important detail. OpenQuery

action in a macro has that expression in the Query Name argument...

Someone previously helped me with a SelectGreeting for my SendObject

action where the email message was

=SelectGreeting() & ",
This is to inform you that the attached contains the cancelled requests.

Please call me to discuss. Thank you!

Derek"

SelectGreeting() is a salutation based on the time of day that the macro is run.

If I can use an expression in this type of argument, would I not be able

to use it in the OpenQuery (query name) argument?