Double Msgboxes

D

Derek Wittman

Good morning,
I have functions that request the business Year and business Period
that the user wants reported. From there, the query runs with that
criteria. The criteria apply to either Request_Date or Complete_Date.
My problem is that the inputbox is coming up twice. What do I need
to do to make it 'save' the value for all calls of the PeriodStart()
function? Code below. Yes, I know I named things without
consideration for appropriate naming practices.

Thanks!
Derek

Option Compare Database

Public Function Busyear() As Integer
Busyear = InputBox("Please enter the YEAR you'd like to examine
(XXXX):")
End Function

Public Function Period() As Integer
Period = InputBox("Please enter the PERIOD you'd like to examine
(XX):")
End Function

Public Function PeriodStart() As Variant
PeriodStart = DLookup("[Start Date]", "Period History", "[Fiscal
Year] = Busyear()") + (Period() - 1) * 28 'watch the wordwrap
MsgBox ("The Period Start Date is:" & PeriodStart)
End Function

SQL for the Query
SELECT [Standards Update Log].[Control Number], [Standards Update
Log].Division, [Standards Update Log].[Date Requested], [Standards
Update Log].Department, [Standards Update Log].[Operation ID],
[Standards Update Log].AislesLevel, [Standards Update Log].[Old
Standard], [Standards Update Log].[New Standard], [Standards Update
Log].[Date Completed]
FROM [Standards Update Log], [Period History]
GROUP BY [Standards Update Log].[Control Number], [Standards Update
Log].Division, [Standards Update Log].[Date Requested], [Standards
Update Log].Department, [Standards Update Log].[Operation ID],
[Standards Update Log].AislesLevel, [Standards Update Log].[Old
Standard], [Standards Update Log].[New Standard], [Standards Update
Log].[Date Completed], Year([Date Requested]), IIf(Year([Date
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28)+1,Int(([Date
Requested]-#1/5/2004#)/28)+1), Year([Date Completed]), IIf(Year([Date
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28)+1,Int(([Date
Completed]-#1/5/2004#)/28)+1)
HAVING (((Year([Date Requested]))=busyear()) AND ((IIf(Year([Date
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28)+1,Int(([Date
Requested]-#1/5/2004#)/28)+1))=period())) OR (((Year([Date
Completed]))=busyear()) AND ((IIf(Year([Date
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28)+1,Int(([Date
Completed]-#1/5/2004#)/28)+1))=period()));
 
N

none

make Request_Date and Complete_Date Global
variables...pass the values 2 them from the input boxes
 
D

Derek Wittman

I'd like to do that, but I am having trouble with putting global
variables in my criteria (I'm not actually doing it in SQL, but I know
that the SQL can recreate the Access queries). It keeps putting
quotes around them. I'm in 2000.

Thanks!
Derek

[email protected] said:
make Request_Date and Complete_Date Global
variables...pass the values 2 them from the input boxes
-----Original Message-----
Good morning,
I have functions that request the business Year and business Period
that the user wants reported. From there, the query runs with that
criteria. The criteria apply to either Request_Date or Complete_Date.
My problem is that the inputbox is coming up twice. What do I need
to do to make it 'save' the value for all calls of the PeriodStart()
function? Code below. Yes, I know I named things without
consideration for appropriate naming practices.

Thanks!
Derek

Option Compare Database

Public Function Busyear() As Integer
Busyear = InputBox("Please enter the YEAR you'd like to examine
(XXXX):")
End Function

Public Function Period() As Integer
Period = InputBox("Please enter the PERIOD you'd like to examine
(XX):")
End Function

Public Function PeriodStart() As Variant
PeriodStart = DLookup("[Start Date]", "Period History", "[Fiscal
Year] = Busyear()") + (Period() - 1) * 28 'watch the wordwrap
MsgBox ("The Period Start Date is:" & PeriodStart)
End Function

SQL for the Query
SELECT [Standards Update Log].[Control Number], [Standards Update
Log].Division, [Standards Update Log].[Date Requested], [Standards
Update Log].Department, [Standards Update Log].[Operation ID],
[Standards Update Log].AislesLevel, [Standards Update Log].[Old
Standard], [Standards Update Log].[New Standard], [Standards Update
Log].[Date Completed]
FROM [Standards Update Log], [Period History]
GROUP BY [Standards Update Log].[Control Number], [Standards Update
Log].Division, [Standards Update Log].[Date Requested], [Standards
Update Log].Department, [Standards Update Log].[Operation ID],
[Standards Update Log].AislesLevel, [Standards Update Log].[Old
Standard], [Standards Update Log].[New Standard], [Standards Update
Log].[Date Completed], Year([Date Requested]), IIf(Year ([Date
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28) +1,Int(([Date
Requested]-#1/5/2004#)/28)+1), Year([Date Completed]), IIf (Year([Date
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28) +1,Int(([Date
Completed]-#1/5/2004#)/28)+1)
HAVING (((Year([Date Requested]))=busyear()) AND ((IIf (Year([Date
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28) +1,Int(([Date
Requested]-#1/5/2004#)/28)+1))=period())) OR (((Year([Date
Completed]))=busyear()) AND ((IIf(Year([Date
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28) +1,Int(([Date
Completed]-#1/5/2004#)/28)+1))=period()));
.
Click to expand...
Click to expand...
 
G

Guest

If anyone else was helping with this, thanks for your time. I actually tried a simple enough workaround. I created a form-based filter that calculated the dates that the period begins and ends. Then, these variables are put into the query appropriately. Thank you anyway

Dere

----- Derek Wittman wrote: ----

I'd like to do that, but I am having trouble with putting globa
variables in my criteria (I'm not actually doing it in SQL, but I kno
that the SQL can recreate the Access queries). It keeps puttin
quotes around them. I'm in 2000

Thanks
Dere

[email protected] said:
make Request_Date and Complete_Date Global
variables...pass the values 2 them from the input boxe
-----Original Message----
Good morning
I have functions that request the business Year and business Perio
that the user wants reported. From there, the query runs with tha
criteria. The criteria apply to either Request_Date or Complete_Date
My problem is that the inputbox is coming up twice. What do I nee
to do to make it 'save' the value for all calls of the PeriodStart(
function? Code below. Yes, I know I named things withou
consideration for appropriate naming practices
Thanks Dere
Option Compare Databas
Public Function Busyear() As Intege
Click to expand...
Busyear = InputBox("Please enter the YEAR you'd like to examin
(XXXX):"
End Functio
Public Function Period() As Intege
Click to expand...
Period = InputBox("Please enter the PERIOD you'd like to examin
(XX):"
End Functio
Public Function PeriodStart() As Varian
Click to expand...
PeriodStart = DLookup("[Start Date]", "Period History", "[Fisca
Year] = Busyear()") + (Period() - 1) * 28 'watch the wordwra
MsgBox ("The Period Start Date is:" & PeriodStart
End Functio
SQL for the Quer
Click to expand...
SELECT [Standards Update Log].[Control Number], [Standards Updat
Log].Division, [Standards Update Log].[Date Requested], [Standard
Update Log].Department, [Standards Update Log].[Operation ID]
[Standards Update Log].AislesLevel, [Standards Update Log].[Ol
Standard], [Standards Update Log].[New Standard], [Standards Updat
Log].[Date Completed
FROM [Standards Update Log], [Period History
GROUP BY [Standards Update Log].[Control Number], [Standards Updat
Log].Division, [Standards Update Log].[Date Requested], [Standard
Update Log].Department, [Standards Update Log].[Operation ID]
[Standards Update Log].AislesLevel, [Standards Update Log].[Ol
Standard], [Standards Update Log].[New Standard], [Standards Updat
Log].[Date Completed], Year([Date Requested]), IIf(Yea ([Dat
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28 +1,Int(([Dat
Requested]-#1/5/2004#)/28)+1), Year([Date Completed]), II (Year([Dat
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28 +1,Int(([Dat
Completed]-#1/5/2004#)/28)+1
HAVING (((Year([Date Requested]))=busyear()) AND ((II (Year([Dat
Requested])=2003,Int(([Date Requested]-#2/2/2003#)/28 +1,Int(([Dat
Requested]-#1/5/2004#)/28)+1))=period())) OR (((Year([Dat
Completed]))=busyear()) AND ((IIf(Year([Dat
Completed])=2003,Int(([Date Completed]-#2/2/2003#)/28 +1,Int(([Dat
Completed]-#1/5/2004#)/28)+1))=period()))
Click to expand...
Click to expand...
 

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

Functions --> Expression 7
How to run an SQL Insert statement in a Module using variables 3
Julian Date function runs too slow 2
Find "Last Friday" function works almost 100% 10
This is a toughie | auto-calculate depreciation | populate unbound 4
Print macro Excel 2003 used to work, now crashes 1
basMedian obtaining quarterly results 5
I need help with this program written in Visual Basic for Excel 2007. 4

Top