1,105,286 Community Members

Worried to Declare a Month

Member Avatar
jovstudios
Junior Poster in Training
53 posts since Mar 2013
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
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

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
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
Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

@AndreRet

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

Member Avatar
QVeen72
Veteran Poster
1,016 posts since Nov 2006
Reputation Points: 104 [?]
Q&As Helped to Solve: 164 [?]
Skill Endorsements: 7 [?]
 
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

Member Avatar
jovstudios
Junior Poster in Training
53 posts since Mar 2013
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
rishif2
Posting Whiz in Training
284 posts since Dec 2012
Reputation Points: 57 [?]
Q&As Helped to Solve: 58 [?]
Skill Endorsements: 4 [?]
 
0
 

then just try

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

hope this helps you

Member Avatar
QVeen72
Veteran Poster
1,016 posts since Nov 2006
Reputation Points: 104 [?]
Q&As Helped to Solve: 164 [?]
Skill Endorsements: 7 [?]
 
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

Member Avatar
AndreRet
Industrious Poster
4,492 posts since Jan 2008
Reputation Points: 362 [?]
Q&As Helped to Solve: 499 [?]
Skill Endorsements: 24 [?]
 
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... :)

Member Avatar
jovstudios
Junior Poster in Training
53 posts since Mar 2013
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Thanks Guys,, Problem Solve. Many Thanks..

Question Answered as of 1 Year Ago by rishif2, QVeen72 and AndreRet
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article