G
Guest
yea basic what the title says running a query by month. Currently im useing
the between criteria
the between criteria
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
Jeff Boyce
We're not there, we can't see your data, nor your query, nor the desired end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month i
wanted to know if there is an more efficient way to do it
there is an ez way so that when the Query is run I can just enter the month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month i
wanted to know if there is an more efficient way to do it
J
Jeff Boyce
If you want to be prompted for the month (e.g., 1-12), use the Month()
function in your query to calculate the month of the date field. Note,
however, that each year has a month = 7, so you'll also need to prompt for
the year (e.g., 2005) -- use the Year() function to calculate the year of
the date field.
When you're done with these, does it actually save keystrokes/time over
entering exact dates?
NOTE: if you are working with dates falling within the current year, you'll
only need to enter "m/d" and <enter> -- Access will assume you mean the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
function in your query to calculate the month of the date field. Note,
however, that each year has a month = 7, so you'll also need to prompt for
the year (e.g., 2005) -- use the Year() function to calculate the year of
the date field.
When you're done with these, does it actually save keystrokes/time over
entering exact dates?
NOTE: if you are working with dates falling within the current year, you'll
only need to enter "m/d" and <enter> -- Access will assume you mean the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the
month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month i
wanted to know if there is an more efficient way to do it
Jeff Boyce said:We're not there, we can't see your data, nor your query, nor the desired
end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
thanks for the responce,
what would the correct santax before a single month
what would the correct santax before a single month
Jeff Boyce said:If you want to be prompted for the month (e.g., 1-12), use the Month()
function in your query to calculate the month of the date field. Note,
however, that each year has a month = 7, so you'll also need to prompt for
the year (e.g., 2005) -- use the Year() function to calculate the year of
the date field.
When you're done with these, does it actually save keystrokes/time over
entering exact dates?
NOTE: if you are working with dates falling within the current year, you'll
only need to enter "m/d" and <enter> -- Access will assume you mean the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the
month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month i
wanted to know if there is an more efficient way to do it
Jeff Boyce said:We're not there, we can't see your data, nor your query, nor the desired
end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
yea basic what the title says running a query by month. Currently im
useing
the between criteria
J
Jeff Boyce
"correct syntax" where? Are you using the Month() and Year() functions?
Check Access HELP for correct syntax on these functions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Check Access HELP for correct syntax on these functions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:thanks for the responce,
what would the correct santax before a single month
Jeff Boyce said:If you want to be prompted for the month (e.g., 1-12), use the Month()
function in your query to calculate the month of the date field. Note,
however, that each year has a month = 7, so you'll also need to prompt
for
the year (e.g., 2005) -- use the Year() function to calculate the year of
the date field.
When you're done with these, does it actually save keystrokes/time over
entering exact dates?
NOTE: if you are working with dates falling within the current year,
you'll
only need to enter "m/d" and <enter> -- Access will assume you mean the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the
month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month
i
wanted to know if there is an more efficient way to do it
:
We're not there, we can't see your data, nor your query, nor the
desired
end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
yea basic what the title says running a query by month. Currently im
useing
the between criteria
G
Guest
Currently while useing the Between () and () when i run the query alittle
prompt opens and asks be more the date span of which i want to limit my
query. when i change it to Month() in the criteria field and run the query it
says " the expression you entered has a function containing the wrong number
of arguments". Which I will assume that im missing something.
What i want to know is if there is a similer way to have a prompt come up
when i run the query and just enter 06/2007 or 06/07 and it would work
prompt opens and asks be more the date span of which i want to limit my
query. when i change it to Month() in the criteria field and run the query it
says " the expression you entered has a function containing the wrong number
of arguments". Which I will assume that im missing something.
What i want to know is if there is a similer way to have a prompt come up
when i run the query and just enter 06/2007 or 06/07 and it would work
Jeff Boyce said:"correct syntax" where? Are you using the Month() and Year() functions?
Check Access HELP for correct syntax on these functions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:thanks for the responce,
what would the correct santax before a single month
Jeff Boyce said:If you want to be prompted for the month (e.g., 1-12), use the Month()
function in your query to calculate the month of the date field. Note,
however, that each year has a month = 7, so you'll also need to prompt
for
the year (e.g., 2005) -- use the Year() function to calculate the year of
the date field.
When you're done with these, does it actually save keystrokes/time over
entering exact dates?
NOTE: if you are working with dates falling within the current year,
you'll
only need to enter "m/d" and <enter> -- Access will assume you mean the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the
month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the month
i
wanted to know if there is an more efficient way to do it
:
We're not there, we can't see your data, nor your query, nor the
desired
end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
yea basic what the title says running a query by month. Currently im
useing
the between criteria
J
John W. Vinson
I have a table that has a date column in it MM/DD/YYYY I want to know if
there is an ez way so that when the Query is run I can just enter the month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
Try
= DateSerial([Enter year:], [Enter month number:], 1) AND < DateSerial([Enter year:], [Enter month number:] + 1, 1)
John W. Vinson [MVP]
J
Jeff Boyce
No. Or at least, not easily...
Again, you'll need to either use separate fields (the expression using
Month() and Year() goes in the field, not the selection criterion.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Again, you'll need to either use separate fields (the expression using
Month() and Year() goes in the field, not the selection criterion.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:Currently while useing the Between () and () when i run the query alittle
prompt opens and asks be more the date span of which i want to limit my
query. when i change it to Month() in the criteria field and run the query
it
says " the expression you entered has a function containing the wrong
number
of arguments". Which I will assume that im missing something.
What i want to know is if there is a similer way to have a prompt come up
when i run the query and just enter 06/2007 or 06/07 and it would work
Jeff Boyce said:"correct syntax" where? Are you using the Month() and Year() functions?
Check Access HELP for correct syntax on these functions.
Regards
Jeff Boyce
Microsoft Office/Access MVP
The Uke said:thanks for the responce,
what would the correct santax before a single month
:
If you want to be prompted for the month (e.g., 1-12), use the Month()
function in your query to calculate the month of the date field.
Note,
however, that each year has a month = 7, so you'll also need to prompt
for
the year (e.g., 2005) -- use the Year() function to calculate the year
of
the date field.
When you're done with these, does it actually save keystrokes/time
over
entering exact dates?
NOTE: if you are working with dates falling within the current year,
you'll
only need to enter "m/d" and <enter> -- Access will assume you mean
the
current year.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a table that has a date column in it MM/DD/YYYY I want to know
if
there is an ez way so that when the Query is run I can just enter
the
month
and year
currently the query says
Between [Enter Start Date of Quarter or Month: (mm/dd/yyyy) ]
And [Enter Finish Date: (mm/dd/yyyy) ]
and i have to enter the begining of the month and the end of the
month
i
wanted to know if there is an more efficient way to do it
:
We're not there, we can't see your data, nor your query, nor the
desired
end
result.
More info, please...
Regards
Jeff Boyce
Microsoft Office/Access MVP
yea basic what the title says running a query by month. Currently
im
useing
the between criteria
G
Guest
Using VB, I would do this is as follows:
1) Create a module that contains variables to store the values I wish to
use as query criteria, a procedure that allows me store these values for
later use using code and a retrieval function that returns these values for
use in a my target query.
Example Module Contents
---------------------------
'variable to store Month Start Date
Private dtMthStart As Date
'variable to store Month End Date
Private dtMthEnd As Date
'******************************************
Sub setGlobalMonthStartEnd(dtDate As Date)
'******************************************
' Used to set Month Start and Month End date values
' based on parameter dtDate for later use by database
'******************************************
dtMthStart = DateSerial(Year(dtDate), Month(dtDate), 1)
dtMthEnd = DateSerial(Year(dtDate), Month(dtDate) + 1, 0)
End Sub
'******************************************
Function dtGetGlobal(strVar As String) As Date
'******************************************
' Returns the value of either Month Start or Month End
' Note: If these values have not been set, it uses
' the current date to return a value
'******************************************
Select Case strVar
Case "MthStart"
If IsNull(gdtMthStart) Then
dtGetGlobal= DateSerial(Year(Date), Month(Date), 1)
Else
dtGetGlobal= dtMthStart
End If
Case "MthEnd"
If IsNull(gdtMthStart) Then
dtGetGlobal = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
dtGetGlobal = dtMthEnd
End If
End Select
End Function
2) Create a query that uses the retrieval function to set the criteria
Sample Query Contents
------------------------------
SELECT FieldDate, FieldX, FieldY FROM TableX WHERE FieldDate BETWEEN
dtGetGlobal("MthStart") AND dtGetGlobal("MthEnd");
3) In the form where I want to generate something based on the query I do
the following:
a) Get a date that is in the month for the desired results and store it
to a local date variable.
b) Call the sub to define the Month Start and End date values using the
local variable.
setGlobalMonthStartEnd dateVariable
c) Use the query to acheive the desired results, be it to create a
report, or perform other processing, based on the query.
Brian M
1) Create a module that contains variables to store the values I wish to
use as query criteria, a procedure that allows me store these values for
later use using code and a retrieval function that returns these values for
use in a my target query.
Example Module Contents
---------------------------
'variable to store Month Start Date
Private dtMthStart As Date
'variable to store Month End Date
Private dtMthEnd As Date
'******************************************
Sub setGlobalMonthStartEnd(dtDate As Date)
'******************************************
' Used to set Month Start and Month End date values
' based on parameter dtDate for later use by database
'******************************************
dtMthStart = DateSerial(Year(dtDate), Month(dtDate), 1)
dtMthEnd = DateSerial(Year(dtDate), Month(dtDate) + 1, 0)
End Sub
'******************************************
Function dtGetGlobal(strVar As String) As Date
'******************************************
' Returns the value of either Month Start or Month End
' Note: If these values have not been set, it uses
' the current date to return a value
'******************************************
Select Case strVar
Case "MthStart"
If IsNull(gdtMthStart) Then
dtGetGlobal= DateSerial(Year(Date), Month(Date), 1)
Else
dtGetGlobal= dtMthStart
End If
Case "MthEnd"
If IsNull(gdtMthStart) Then
dtGetGlobal = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
dtGetGlobal = dtMthEnd
End If
End Select
End Function
2) Create a query that uses the retrieval function to set the criteria
Sample Query Contents
------------------------------
SELECT FieldDate, FieldX, FieldY FROM TableX WHERE FieldDate BETWEEN
dtGetGlobal("MthStart") AND dtGetGlobal("MthEnd");
3) In the form where I want to generate something based on the query I do
the following:
a) Get a date that is in the month for the desired results and store it
to a local date variable.
b) Call the sub to define the Month Start and End date values using the
local variable.
setGlobalMonthStartEnd dateVariable
c) Use the query to acheive the desired results, be it to create a
report, or perform other processing, based on the query.
Brian M
G
Guest
Note: Code Correction for function in previous email:
'******************************************
Function dtGetGlobal(strVar As String) As Date
'******************************************
' Returns the value of either Month Start or Month End
' Note: If these values have not been set, it uses
' the current date to return a value
'******************************************
Select Case strVar
Case "MthStart"
If IsNull(dtMthStart) Then '----- not gdtMthStart
dtGetGlobal= DateSerial(Year(Date), Month(Date), 1)
Else
dtGetGlobal= dtMthStart
End If
Case "MthEnd"
If IsNull(dtMthStart) Then '----- not gdtMthEnd >>>>>
dtGetGlobal = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
dtGetGlobal = dtMthEnd
End If
End Select
Brian M
'******************************************
Function dtGetGlobal(strVar As String) As Date
'******************************************
' Returns the value of either Month Start or Month End
' Note: If these values have not been set, it uses
' the current date to return a value
'******************************************
Select Case strVar
Case "MthStart"
If IsNull(dtMthStart) Then '----- not gdtMthStart
dtGetGlobal= DateSerial(Year(Date), Month(Date), 1)
Else
dtGetGlobal= dtMthStart
End If
Case "MthEnd"
If IsNull(dtMthStart) Then '----- not gdtMthEnd >>>>>
dtGetGlobal = DateSerial(Year(Date), Month(Date) + 1, 0)
Else
dtGetGlobal = dtMthEnd
End If
End Select
Brian M