Searching via Month and Year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two combo boxes in a form, cbxMonth and cbxYear that requires user to
input the month and year. Based on this input, a report will be displayed
showing transactions for the particular period. This is my code, but it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Pls help!

PS. I have another form that has a combo box to input date, and the code
below works.

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")
 
You forgot a " AND ". Also, I expect your Me.Month is a number 1-12 and
Me.Year is an integer:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & " AND Month([TransactionDate]) =" & _
Me.Month _
& " And Year(TransactionDate) = " & Me.Year
 
Hi, Thanks a lot for pointing out the error. I am using Row Source Type:
Value List for both cbx. My values for Year is 2003, 2004,..., etc... and
Month is 1,2,3..., etc.

However, the code still doesn't run... This is the code I used:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "AND Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Also, is it possible to use "January, February, March, ... etc" as the value
list for cbxMonth? How do I link 1 to January, 2 to February, and so forth?

Appreciate your help.

Duane Hookom said:
You forgot a " AND ". Also, I expect your Me.Month is a number 1-12 and
Me.Year is an integer:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & " AND Month([TransactionDate]) =" & _
Me.Month _
& " And Year(TransactionDate) = " & Me.Year

--
Duane Hookom
MS Access MVP


spacerocket said:
I have two combo boxes in a form, cbxMonth and cbxYear that requires user
to
input the month and year. Based on this input, a report will be displayed
showing transactions for the particular period. This is my code, but it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Pls help!

PS. I have another form that has a combo box to input date, and the code
below works.

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
 
Sorry, I found out that I don't need to format the date. The code is okay now
:)

However, is it possible to use "January, February, March, ... etc" as the
value
list for cbxMonth? How do I link 1 to January, 2 to February, and so forth?

Thanks again.

spacerocket said:
Hi, Thanks a lot for pointing out the error. I am using Row Source Type:
Value List for both cbx. My values for Year is 2003, 2004,..., etc... and
Month is 1,2,3..., etc.

However, the code still doesn't run... This is the code I used:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "AND Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Also, is it possible to use "January, February, March, ... etc" as the value
list for cbxMonth? How do I link 1 to January, 2 to February, and so forth?

Appreciate your help.

Duane Hookom said:
You forgot a " AND ". Also, I expect your Me.Month is a number 1-12 and
Me.Year is an integer:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & " AND Month([TransactionDate]) =" & _
Me.Month _
& " And Year(TransactionDate) = " & Me.Year

--
Duane Hookom
MS Access MVP


spacerocket said:
I have two combo boxes in a form, cbxMonth and cbxYear that requires user
to
input the month and year. Based on this input, a report will be displayed
showing transactions for the particular period. This is my code, but it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Pls help!

PS. I have another form that has a combo box to input date, and the code
below works.

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
Click to expand...
 
Set these properties in your month combo box
Column Count: 2
Row Source Type: Value List
Row Source: 1, "January",2,"February",3,"March",...
Bound Column: 1
Column Widths: 0;1

--
Duane Hookom
MS Access MVP


spacerocket said:
Sorry, I found out that I don't need to format the date. The code is okay
now
:)

However, is it possible to use "January, February, March, ... etc" as the
value
list for cbxMonth? How do I link 1 to January, 2 to February, and so
forth?

Thanks again.

spacerocket said:
Hi, Thanks a lot for pointing out the error. I am using Row Source Type:
Value List for both cbx. My values for Year is 2003, 2004,..., etc...
and
Month is 1,2,3..., etc.

However, the code still doesn't run... This is the code I used:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "AND Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Also, is it possible to use "January, February, March, ... etc" as the
value
list for cbxMonth? How do I link 1 to January, 2 to February, and so
forth?

Appreciate your help.

Duane Hookom said:
You forgot a " AND ". Also, I expect your Me.Month is a number 1-12 and
Me.Year is an integer:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & " AND Month([TransactionDate]) =" & _
Me.Month _
& " And Year(TransactionDate) = " & Me.Year

--
Duane Hookom
MS Access MVP


I have two combo boxes in a form, cbxMonth and cbxYear that requires
user
to
input the month and year. Based on this input, a report will be
displayed
showing transactions for the particular period. This is my code, but
it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Pls help!

PS. I have another form that has a combo box to input date, and the
code
below works.

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
Click to expand...
 
spacerocket said:
I have two combo boxes in a form, cbxMonth and cbxYear that requires user to
input the month and year. Based on this input, a report will be displayed
showing transactions for the particular period. This is my code, but it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")
Click to expand...


The Month function returns the number of the month (1 - 12),
not the name of the month (which is language dependent).

Furthermore, if the Year text box is used to enter a year
number, it is not a date and will not be converted to the
date your Format the way you might like.

I have no idea what you expect your users to enter in the
Month text box, but if it's the month number and the Year
text box has a four digit year number, then your code could
be:

strWhere = "[BankID] = " & Me.[BankID] & _
" AND [CompanyID] = " & Me.[CompanyID] & _
" AND Month([TransactionDate]) =" & Me.Month _
" AND Year(TransactionDate) = " & Me.Year

If it's the text boes contain something other than my
guesses, I'll have to know what they are before I can figure
out how to use them.
 
Thanks a lot!

Duane Hookom said:
Set these properties in your month combo box
Column Count: 2
Row Source Type: Value List
Row Source: 1, "January",2,"February",3,"March",...
Bound Column: 1
Column Widths: 0;1

--
Duane Hookom
MS Access MVP


spacerocket said:
Sorry, I found out that I don't need to format the date. The code is okay
now
:)

However, is it possible to use "January, February, March, ... etc" as the
value
list for cbxMonth? How do I link 1 to January, 2 to February, and so
forth?

Thanks again.

spacerocket said:
Hi, Thanks a lot for pointing out the error. I am using Row Source Type:
Value List for both cbx. My values for Year is 2003, 2004,..., etc...
and
Month is 1,2,3..., etc.

However, the code still doesn't run... This is the code I used:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "AND Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Also, is it possible to use "January, February, March, ... etc" as the
value
list for cbxMonth? How do I link 1 to January, 2 to February, and so
forth?

Appreciate your help.

:

You forgot a " AND ". Also, I expect your Me.Month is a number 1-12 and
Me.Year is an integer:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & " AND Month([TransactionDate]) =" & _
Me.Month _
& " And Year(TransactionDate) = " & Me.Year

--
Duane Hookom
MS Access MVP


I have two combo boxes in a form, cbxMonth and cbxYear that requires
user
to
input the month and year. Based on this input, a report will be
displayed
showing transactions for the particular period. This is my code, but
it
doesn't work:

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "Month([TransactionDate]) =" & _
Format(Me.Month, "mmmm") _
& " And Year(TransactionDate) = " & Format(Me.Year, "yyyy")

Pls help!

PS. I have another form that has a combo box to input date, and the
code
below works.

strWhere = "[BankID] = " & Me.[BankID] & " AND [CompanyID] = " & _
Me.[CompanyID]
strWhere = strWhere & "And TransactionDate Between " & _
Format(Me.BeginDate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.EndDate, "\#m\/d\/yyyy\#")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top