Worried to Declare a Month

Hello Guys, I need Your Help,, im just Worried for declaring the Month of January, Because whenI search Month of January , The Month of October, November, And december, Would Appear.
I want to search is The Month of January Only.

This is My Code.

January = 1
February = 2
March = 3
April = 4
May = 5
June = 6
July = 7
August = 8
September = 9
October = 10
November = 11
December = 12

Dim listx As ListItem
Dim rs As New ADODB.Recordset
'January ----------------------------------------------------------------------------------------------
If Combo1.Text = "January" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & January & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
monthlyreport.Enabled = True
End If
End If
'February ------------------------------------------------------------------------------------------------
If Combo1.Text = "February" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & February & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'march----------------------------------------------------------------------------------------------------
If Combo1.Text = "March" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & March & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'April ----------------------------------------------------------------------------------------------
If Combo1.Text = "April" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & April & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'May ------------------------------------------------------------------------------------------------
If Combo1.Text = "May" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & May & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'June ----------------------------------------------------------------------------------------------------
If Combo1.Text = "June" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & June & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'July ----------------------------------------------------------------------------------------------
If Combo1.Text = "July" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & July & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'August ------------------------------------------------------------------------------------------------
If Combo1.Text = "August" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & August & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'September----------------------------------------------------------------------------------------------------
If Combo1.Text = "September" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & September & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'October ----------------------------------------------------------------------------------------------
If Combo1.Text = "October" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & October & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'November----------------------------------------------------------------------------------------------------
If Combo1.Text = "November" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & November & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
'December ------------------------------------------------------------------------------------------------
If Combo1.Text = "December" Then
    rs.Open "Select * from Dailyrecord where purchase_date Like '" & December & "%'", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
dailyreport.Enabled = True
monthlyreport.Enabled = True
End If
End If
monthlyreport.Enabled = True

Help me Guys, Thanks in Adavance

4

Contributors

9

Replies

22 Hours

Discussion Span

2 Months Ago

Last Updated

27

Views

Question
Answered

jovstudios

Newbie Poster

23 posts since Mar 2013

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

2 Months Ago

0

January = 1
February = 2
March = 3
April = 4
May = 5
June = 6
July = 7
August = 8
September = 9
October = 10
November = 11
December = 12

first of all i dont know why you are using that code

the other stuff is make proper use of ElseIf statement that will help you for producing result for specific month only(i.e it will not check other conditions , once it found true condition)

Because whenI search Month of January , The Month of October, November, And december, Would Appear.

this is because you are working with like operator and when it 1(january) , then it will also return the value starting from 1(i.e 10,11,12 that are for oct. , nov. , dec. respectively)

to get rid of this problem you can use of like operator in following manner

rs.Open "Select * from Dailyrecord where purchase_date Like '" & January & "/%'"

hope this helps you to solve the issue

rishif2

Junior Poster

153 posts since Dec 2012

Reputation Points: 54

Solved Threads: 31

Skill Endorsements: 3

2 Months Ago

0

a Lot of code which can be done in less lines using OOP. :)
Try the following - eliminate LIKE in your statement and use the actual month selected. Because of LIKE you are getting other months as well because it contains characters that forms part of January...

First off, cut this part, you don't need it...

January = 1
February = 2
March = 3
April = 4
May = 5
June = 6
July = 7
August = 8
September = 9
October = 10
November = 11
December = 12

''Delete, not needed...

Now, using OOP, code the following...

Dim strMonth As String

strMonth = Combo1.Text
''USE THIS HERE TO GET A VALUE FROM THE USER THAT WAS SELECTED IN THE COMBOBOX...

Dim listx As ListItem
Dim rs As New ADODB.Recordset

rs.Open "Select * from Dailyrecord where purchase_date ='" & strMonth & "'", acd, adOpenForwardOnly, adLockOptimistic
If rs.RecordCount = 0 Then
    MsgBox "No records found for the month of " & strMonth & ".", vbInformation, "Details"
        Else
    ListView1.ListItems.clear
    While rs.EOF <> True
        Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))
        listx.SubItems(1) = rs.Fields("BarCode")
        listx.SubItems(2) = rs.Fields("ProductName")
        listx.SubItems(3) = rs.Fields("Quantity")
        listx.SubItems(4) = rs.Fields("Price")
        listx.SubItems(5) = rs.Fields("TotalPrice")
        listx.SubItems(6) = rs.Fields("purchase_date")

        rs.MoveNext
    Wend

monthlyreport.Enabled = True
rs.Close
End If

AndreRet

Industrious Poster

4,706 posts since Jan 2008

Reputation Points: 391

Solved Threads: 481

Skill Endorsements: 20

2 Months Ago

0

@AndreRet

I think that he is using the field named purchase_date as mm/dd/yyyy that's why he is using like %

rishif2

Junior Poster

153 posts since Dec 2012

Reputation Points: 54

Solved Threads: 31

Skill Endorsements: 3

2 Months Ago

0

Hi,

In your table, what type of field is "purchase_date"...?
If it is a Date field, you dont have to use "Like" operator.. you need to query using Date Functions...
What database it is..? Access..? MSSQL..? Oracle..?

Regards
Veena

QVeen72

Veteran Poster

1,017 posts since Nov 2006

Reputation Points: 118

Solved Threads: 164

Skill Endorsements: 5

2 Months Ago

0

Yes in my Database. purchase_date is the data field type. Then I am Using Database MS Access 2003

jovstudios

Newbie Poster

23 posts since Mar 2013

Reputation Points: 0

Solved Threads: 0

Skill Endorsements: 0

2 Months Ago

0

then just try

rs.Open "Select * from Dailyrecord where purchase_date Like '" & January & "/%'" (if it is january)

hope this helps you

rishif2

Junior Poster

153 posts since Dec 2012

Reputation Points: 54

Solved Threads: 31

Skill Endorsements: 3

2 Months Ago

Show Comments

1

Hi,

In that case, you need to query like this:

use AndreRet's code... Modify that sql query this way.... use Month() ...

rs.Open "Select * from Dailyrecord where Month(purchase_date) = " _
    & Combo1.ItemData(Combo1.ListIndex) , acd, adOpenForwardOnly

Fill the Combo Using this Code:

Combo1.Clear
Combo1.AddItem "January"
Combo1.ItemData(Combo1.NewIndex) = 1
Combo1.AddItem "February"
Combo1.ItemData(Combo1.NewIndex) = 2
'
......... For all months..
Combo1.AddItem "December"
Combo1.ItemData(Combo1.NewIndex) = 12

Regards
Veena

QVeen72

Veteran Poster

1,017 posts since Nov 2006

Reputation Points: 118

Solved Threads: 164

Skill Endorsements: 5

2 Months Ago

0

Qveen hit the nail on the head. Why use tons of ifs and elses even if the field is id date format yy/mm... :)

AndreRet

Industrious Poster