Worried to Declare a Month

Question

jovstudios -3 Junior Poster in Training

11 Years Ago

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

visual-basic

4 Contributors
9 Replies
147 Views
22 Hours Discussion Span
Latest Post 11 Years Ago Latest Post by jovstudios

rishif2 57 Posting Whiz in Training

11 Years Ago

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

Edited 11 Years Ago by rishif2

AndreRet 526 Senior Poster

11 Years Ago

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

QVeen72 104 Posting Shark

11 Years Ago

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

AndreRet commented: exactly! Well demonstrated... +13

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

rishif2 57 Posting Whiz in Training · Answer 1 · 2013-03-14T07:06:03+00:00

@AndreRet

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

QVeen72 104 Posting Shark · Answer 2 · 2013-03-14T09:56:38+00:00

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

jovstudios -3 Junior Poster in Training · Answer 3 · 2013-03-14T13:42:36+00:00

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

rishif2 57 Posting Whiz in Training · Answer 4 · 2013-03-14T14:20:28+00:00

then just try

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

hope this helps you

AndreRet 526 Senior Poster · Answer 5 · 2013-03-14T17:15:55+00:00

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

jovstudios -3 Junior Poster in Training · Answer 6 · 2013-03-15T04:51:31+00:00

jovstudios -3 Junior Poster in Training

11 Years Ago

Thanks Guys,, Problem Solve. Many Thanks..