-
Aug 27th, 2014, 03:17 AM
#1
Thread Starter
Enjoy the moment
[RESOLVED] do i need to use here 2 where clause ?
i am trying to figure out how do i check if a customer has a birthday today based on the selected text
how ever i already have a where clause that i check the date
do i need to use 2 where clause also for the customer name?
i got a little confused
this is the code i am using
Code:
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM Customers WHERE DAY(DateBirth) = DAY(#" & MyDate(Now) & "#) " & _
"AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
If Not rs.EOF Then
MsgBox "HappyBirthday To " & " " & txtSubject.Text
rs.Close
End If
any help will be appreciated
regards salsa31
-
Aug 27th, 2014, 03:21 AM
#2
Re: do i need to use here 2 where clause ?
I seem to remember the answer has been given to you before try looking through your old posts.
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Aug 27th, 2014, 03:25 AM
#3
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
hey NW i dont think for this question
-
Aug 27th, 2014, 03:50 AM
#4
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
well i tried this but something still missing
Code:
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = DAY(#" & MyDate(Now) & "#) " & _
"AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
If Not rs.EOF Then
MsgBox "HappyBirthday To " & " " & txtSubject.Text
rs.Close
End If
-
Aug 27th, 2014, 04:35 AM
#5
Frenzied Member
Re: do i need to use here 2 where clause ?
why don't you try datediff function .and you need to check diffdate is less than and equal to 1 .
Code:
DiffDate = DateDiff("d", Date, EDate)
-
Aug 27th, 2014, 04:40 AM
#6
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
-
Aug 27th, 2014, 06:14 AM
#7
Re: do i need to use here 2 where clause ?
Using your hairdresser database as an example
Code:
Private Sub Form_Load()
'Return the number of treatments by a particular customer
Dim ConnectionString As String, person As String, treatment As String, calend As String, calStart As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source= " & App.Path & "\db1.mdb"
cn.Open ConnectionString
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
person = "salsa"
calStart = "09:15:00"
calend = "10:00:00"
treatment = "haircut"
rs.Open "CalendarHistory", cn, adOpenKeyset, adLockPessimistic, adCmdTable
'Count (CalTreatment)
ssql = "Select * FROM CalendarHistory where CalCust = '" & person & "'" & " AND CalStart='" & calStart & "'" & " AND CalEnd='" & calend & "'"
Set rs = cn.Execute(ssql)
MsgBox (rs.GetString)
rs.Close
cn.Close
End Sub
See this line
Code:
ssql = "Select * FROM CalendarHistory where CalCust = '" & person & "'" & " AND CalStart='" & calStart & "'" & " AND CalEnd='" & calend & "'"
That is retrieving the customer whose name is "salsa" AND their appointment starts at "09:15:00" AND ends at "10:00:00".
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Aug 27th, 2014, 08:59 AM
#8
Re: do i need to use here 2 where clause ?
Originally Posted by Nightwalker83
I seem to remember the answer has been given to you before try looking through your old posts.
Salsa....yes, please check your old posts...I gave you an example on how to use datediff to do just what you wanted to do. As I told you before, as well, I have a program (a couple, actually), that runs early mornings and sends me emails as birthday reminders 10 days out, 9 days out, etc.
Sammi
-
Aug 27th, 2014, 09:18 AM
#9
Re: do i need to use here 2 where clause ?
I also did a couple programs using an msflexgrid to temporarily hold all the 'data' on a person, and used GMAIL to send me the notification....here's the section that populates the grid using datePart() instead of datediff():
Code:
rs.Open "select * from bday", cnn, adOpenKeyset, adLockPessimistic
Do While Not rs.EOF
If DatePart("y", rs!bday) - DatePart("y", Date) < 11 And DatePart("y", rs!bday) - DatePart("y", Date) > -1 Then 'starts 10 days out, down to last day
iAge = Year(Date) - Year(rs!bday)
grid1.Rows = grid1.Rows + 1 'populate the grid
grid1.TextMatrix(grid1.Rows - 1, 0) = "BIRTHDAY"
grid1.TextMatrix(grid1.Rows - 1, 1) = rs!firstname
grid1.TextMatrix(grid1.Rows - 1, 3) = rs!lastname
grid1.TextMatrix(grid1.Rows - 1, 4) = rs!bday
grid1.TextMatrix(grid1.Rows - 1, 5) = iAge
grid1.TextMatrix(grid1.Rows - 1, 6) = rs!email
grid1.TextMatrix(grid1.Rows - 1, 8) = DatePart("y", rs!bday) - DatePart("y", Date)
End If
rs.movenext
loop
rs.close
Then I called the sub to send the email for all entries in the grid:
Code:
sendStuffToEmailModule
And this is what that sub looks like:
[codePrivate Sub sendStuffToEmailModule()
Dim x As Integer
Dim typeDay As String
Dim haveHas As String
Dim dayDays As String
For x = 0 To grid1.Rows - 1
typeDay = grid1.TextMatrix(x, 0)
Select Case grid1.TextMatrix(x, 8)
Case "1"
dayDays = " day on "
Case "0"
dayDays = " days(TODAY) on "
Case Else
dayDays = " days on "
End Select
GmailSend "~MY EMAIL LOGIN USER ID GOES HERE ~", "~MY EMAIL ACCT PWD GOES HERE", "~MY EMAIL GOES HERE~ ", "UPCOMING " & typeDay, grid1.TextMatrix(x, 1) & " " & grid1.TextMatrix(x, 3) _
& " has a " & grid1.TextMatrix(x, 0) & " in " & grid1.TextMatrix(x, 8) & dayDays & WeekdayName(Weekday(Date + grid1.TextMatrix(x, 8))) & _
", " & Day(grid1.TextMatrix(x, 4)) & " " & MonthName(Month(grid1.TextMatrix(x, 4))) & _
" AGE-" & grid1.TextMatrix(x, 5) & " EMAIL: " & grid1.TextMatrix(x, 6)
Next x
End Sub[/code]
The gmailSend function can be found on the forum, but as you are not using email, instead of calling that function (sendStuffToEmailModule), you can use a messagebox, popup with textbox or listbox, or any way you want to be notified.
-
Aug 27th, 2014, 09:54 AM
#10
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
hey sami why i need to use datediff?
this code that i posted workes fine when i load the birthday form
but what i need is to check if a single customer has a birthday or not.
E.X let say i am making a appointment for haircut for salsa
now when i choose salsa from the list of customers if salsa has a birthday today then a msg will pop out
saying "salsa has a birthday today"
my question was can or i need to use 2 where clause to achieve this goal
-
Aug 27th, 2014, 09:55 AM
#11
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
this is not working i tried it
Code:
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = DAY(#" & MyDate(Now) & "#) " & _
"AND MONTH(DateBirth) = MONTH(#" & MyDate(Now) & "#)", CN
If Not rs.EOF Then
MsgBox "HappyBirthday To " & " " & txtSubject.Text
rs.Close
End If
-
Aug 27th, 2014, 10:16 AM
#12
Re: do i need to use here 2 where clause ?
try this:
Code:
Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
If Not rs.EOF Then
MsgBox "HappyBirthday To " & " " & txtSubject.Text
rs.Close
End If
-
Aug 27th, 2014, 10:29 AM
#13
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
nope
it is still skipping the birhday of the customer
-
Aug 27th, 2014, 10:41 AM
#14
Re: do i need to use here 2 where clause ?
Not in MY database...works fine for me. Are you checking for ucase/lcase, that is, does the upper and lower case of FullName (in your database) match exactly what is in txtSubject.text?
-
Aug 27th, 2014, 10:41 AM
#15
Re: do i need to use here 2 where clause ?
That Red & should be AND
To answer the thread question no, you do not use two where clauses. If and when you need to filter by more than one thing you use AND or you use OR depending on you needs. You may even need to use both in some cases and you may need to group them in ().
The basic logic of this has been explained many times in several of your previous threads.
-
Aug 27th, 2014, 11:06 AM
#16
Re: do i need to use here 2 where clause ?
Originally Posted by DataMiser
That Red & should be AND
Oh yeah, I didn't 'fix' that first part of the line....I DO have 'AND' in MY query.
Good catch.
-
Aug 27th, 2014, 11:55 AM
#17
Re: do i need to use here 2 where clause ?
Another thing that is important, especially when you are trying to find an issue in a query is rather than do this
Code:
rs.Open "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
Do this
Code:
strSQL="SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
rs.Open strSQL
which allows you to insert a Debug message between the two lines and examine the actual query that is being passed to the database. You can even copy the query from the debug window and try it directly in Access or SQL server and sometimes get a better indication of what the problem is an how to fix it.
-
Aug 27th, 2014, 12:57 PM
#18
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
sry DM sir this code dosnt work also
Code:
StrSql = "SELECT * FROM Customers WHERE FullName = '" & txtSubject.Text & "' & DAY(DateBirth) = '" & Day(Date) & "' AND MONTH(DateBirth) = '" & Month(Date) & "',cn"
rs.Open StrSql
Error:expected: end of statement
-
Aug 27th, 2014, 02:14 PM
#19
Re: do i need to use here 2 where clause ?
Salsa...you didn't listen to DM....this works:
Code:
StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'", CN
rs.Open StrSql
-
Aug 27th, 2014, 02:17 PM
#20
Re: do i need to use here 2 where clause ?
Apparently you just copied that code from post #17. That was just an example of how to write code in order to be able to use debug.print to show your query. The actual query was from a previous post which did not work.
Don't copy and paste. Read post #13 and change your query to correct the issue
Or I guess now you can copy and paste the code from #19 but you really are not going to be able to do this on your own if you do not read the posts and try to understand how they apply rather than just pasted the code and then posting again that it did not work.
-
Aug 27th, 2014, 02:19 PM
#21
Re: do i need to use here 2 where clause ?
It READS:
SELECT everything from the customers table WHERE the fullname field is equal to the text value of txtSubject AND the day of datebirth is equal to the day of today AND the month of datebirth is equal to the day of today.
-
Aug 27th, 2014, 02:53 PM
#22
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
Or I guess now you can copy and paste the code from #19 but you really are not going to be able to do this on your own if you do not read the posts and try to understand how they apply rather than just pasted the code and then posting again that it did not work
i know DM i have Dyslexia so i am trying to do my best
if you break the code for me i will surely understand
Salsa...you didn't listen to DM....this works:
i tried it gave me a error here
-
Aug 27th, 2014, 03:11 PM
#23
Re: do i need to use here 2 where clause ?
Post what you tried?
There is an error in Sams code as well, that ,CN should not be on the variable assignment, it should be on the end of the open statement.
If you typed it rather than pasting it you would have probably saw this yourself
I am partly at fault there too as I just pasted that code into the post above without editing the end of the line, can't see it when reading the post without scrolling over
Var=" whatever"
rs.open var,cn
Last edited by DataMiser; Aug 27th, 2014 at 03:16 PM.
-
Aug 27th, 2014, 03:45 PM
#24
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
instead of rs.open i use cn.execute
but how will it check if i use only 1 where clause ?
-
Aug 27th, 2014, 04:21 PM
#25
Re: do i need to use here 2 where clause ?
Maybe the problem is that you do not understand English very well?
Look at Sams post #21
When you use AND you are telling it that you want to check for more than one thing
Imagine a set of If statements
Code:
If Name="somename" Then
If Sex="Male" Then
If Age>30 Then
Show Me this record
Else
Do not show this record
End If
Else
Do not show this record
End If
Else
Do not show this record
End If
This could be written
Code:
If Name="somename" AND Sex="Male" AND Age>30 Then
Show Me this record
Else
Do not show this record
End If
A where clause to do the same thing would be
Code:
Where Name='somename' AND Sex='Male' AND Age>30
You will only get records where everything there matches because you said you want it to match this AND this AND this
You really need to do some reading and then do some trial and error coding yourself to try and understand this very basic and very simple logic
-
Aug 27th, 2014, 04:27 PM
#26
Re: do i need to use here 2 where clause ?
Originally Posted by salsa31
instead of rs.open i use cn.execute
but how will it check if i use only 1 where clause ?
Why would you change it from RS.Open to CN.Execute?
Your first post is using RS.Open so I would expect that you would continue to do that rather than change it to CN.Execute
Is it that you do not understand how to use these two methods? CN.Execute is fine though more limited than RS.Open. Either would work in this case if coded properly.
Of course now I have absolutely no idea what your code might look like. You said you tried what was posted but nothing has been posted using CN.Execute so who knows what you are actually doing.
-
Aug 27th, 2014, 06:20 PM
#27
Re: do i need to use here 2 where clause ?
oops, sorry...darn cut/paste...was trying to merge salsa's code with mine...as DM says:
Code:
StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'"
rs.Open StrSql, CN
-
Aug 27th, 2014, 07:04 PM
#28
Re: do i need to use here 2 where clause ?
salsa31,
Is the DateBirth field in the database actually of datatype Date/Time or is it a Text field?
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Aug 28th, 2014, 01:57 AM
#29
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
hey sam where is the if condition? Post#27
Code:
Is it that you do not understand how to use these two methods? CN.Execute is fine though more limited than RS.Open. Either would work in this case if coded properly.
Of course now I have absolutely no idea what your code might look like. You said you tried what was posted but nothing has been posted using CN.Execute so who knows what you are actually doing.
i know how to use a little
all i am trying to do is to check if a customer has a birthday today and then pop up a msgbox
this what happens when i click on the listiew
Code:
Private Sub LsVw_ItemClick(ByVal Item As XtremeSuiteControls.ListViewItem)
txtSubject.Text = LsVw.SelectedItem.SubItems(1)
CheckBirthDay
Code:
StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & Day(Date) & "' AND MONTH(DateBirth) = '" & Month(Date) & "'"
If Not rs.EOF Then
MsgBox "yes"
CN.Execute StrSql
-
Aug 28th, 2014, 07:21 AM
#30
Re: do i need to use here 2 where clause ?
You REALLY have extremely little knowledge of how to code in VB. Like I suggested a long time ago, go to class, get a book, STUDY!
Code:
StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & DAY(Date) & "' AND MONTH(DateBirth) = '" & MONTH(Date) & "'"
'Like DM said, put a print statement, or msgbox, or something so you can see precisely what is being sent to your database.
'You can correct 90% of your errors right here. For example:
'MsgBox StrSql
rs.Open StrSql, CN
If Not rs.EOF Then
MsgBox "HappyBirthday To " & " " & txtSubject.Text
End If
rs.Close
-
Aug 28th, 2014, 07:28 AM
#31
Re: do i need to use here 2 where clause ?
It would behoove you (that means, it would be to your benefit) if you would download the "Black Book" for Visual Basic 6, available HERE. Go through it, page by page, you can learn a lot from this FREE DOWNLOAD.
-
Aug 28th, 2014, 09:28 AM
#32
Re: do i need to use here 2 where clause ?
Why on earth did you do this. Is RS even a valid object at the point where you reference it?
Code:
StrSql = "SELECT * FROM Customers where fullname = '" & txtSubject.Text & "' and DAY(DateBirth) = '" & Day(Date) & "' AND MONTH(DateBirth) = '" & Month(Date) & "'"
If Not rs.EOF Then
MsgBox "yes"
CN.Execute StrSql
Also surely by now you know that to get data from a CN.Execute you have to set an rs= to it. Of course what you should have done was used RS open like has been shown in almost every post of this thread.
Related to the message that shows the strsql I would recommend using debug.print rather than msgbox. This allows the query to be copied from the debug window at the bottom and pasted into access or the forums if need be and allows you to see it clearly without the need to have a msgbox popping up on the screen.
-
Aug 28th, 2014, 09:46 AM
#33
Re: do i need to use here 2 where clause ?
Originally Posted by DataMiser
Related to the message that shows the strsql I would recommend using debug.print rather than msgbox. This allows the query to be copied from the debug window at the bottom and pasted into access or the forums if need be and allows you to see it clearly without the need to have a msgbox popping up on the screen.
Good point...."I" prefer the msgbox as can "I" can usually immediately see the issue within the message; however, beginners (or experienced developers using complex SQLs) may elect your alternative. -Sammi
-
Aug 28th, 2014, 09:48 AM
#34
Thread Starter
Enjoy the moment
Re: do i need to use here 2 where clause ?
Also surely by now you know that to get data from a CN.Execute you have to set an rs= to it. Of course what you should have done was used RS open like has been shown in almost every post of this thread.
yes i know sir
like i said before i have Dyslexia so it takes me a while to understand
tnk you very much for your help
sami i download that pdf hope to get it right next time
tnk you for your help the post 30# works like a charm
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|