Searching via Month and Year

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\#")
 
D

Duane Hookom

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
 
G

Guest

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...
 
G

Guest

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...
 
D

Duane Hookom

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...
 
M

Marshall Barton

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.
 
G

Guest

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...
 

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

Search Record by Month and Year for Report 4
Separate page for each year 1
Date By Month Filter 3
report generated by comboboxes 1
Filter Specific Date in Access. 0
Calender report conversion. 9
Dates swapping 3
No data report 2

Top