UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Proble Altering A Recordset, Access 2016    
 
   
ordnance1
post Feb 25 2018, 03:30 PM
Post#1



Posts: 567
Joined: 7-May 11



The code below woks as written but I have been unsuccessful at altering the Where clause to in include tblDaysOff.Status =1

CODE
st.Open "SELECT DISTINCT tblDaysOff.VacDate, 11 AS Colr, tblDaysOff.Status, tblDaysOff.EmpID " & _
     "FROM tblDaysOff WHERE tblDaysOff.EmpID= " & Me.cmbEmpName.Column(0) & " AND " & "Year([VacDate])= " & Chr(39) & lblYear & Chr(39) & "ORDER BY tblDaysOff.VacDate;", CurrentProject.Connection, , , adCmdText
Go to the top of the page
 
MadPiet
post Feb 25 2018, 04:12 PM
Post#2



Posts: 2,588
Joined: 27-February 09



What's the data type of tblDaysOff.Status?

Go to the top of the page
 
DanielPineault
post Feb 25 2018, 04:15 PM
Post#3


UtterAccess VIP
Posts: 6,270
Joined: 30-June 11



You're missing a space after your second chr(39) and the ORDER BY

Try something like
CODE
    st.Open "SELECT DISTINCT tblDaysOff.VacDate, 11 AS Colr, tblDaysOff.Status, tblDaysOff.EmpID " & _
               "FROM tblDaysOff " & _
               "WHERE ((tblDaysOff.EmpID=" & Me.cmbEmpName.Column(0) & ") AND (Year([VacDate])=" & Chr(39) & lblYear & Chr(39) & ") AND (tblDaysOff.Status=1)) " & _
               "ORDER BY tblDaysOff.VacDate;", CurrentProject.Connection, , , adCmdText



I also question the need of the chr(39) in the first palce since Year() returns an Integer value (numeric).
CODE
    st.Open "SELECT DISTINCT tblDaysOff.VacDate, 11 AS Colr, tblDaysOff.Status, tblDaysOff.EmpID " & _
               "FROM tblDaysOff " & _
               "WHERE ((tblDaysOff.EmpID=" & Me.cmbEmpName.Column(0)  & ") AND (Year([VacDate])=" & lblYear & ") AND (tblDaysOff.Status=1)) " & _
               "ORDER BY tblDaysOff.VacDate;", CurrentProject.Connection, , , adCmdText
Go to the top of the page
 
ordnance1
post Feb 25 2018, 05:40 PM
Post#4



Posts: 567
Joined: 7-May 11



Numeric
Go to the top of the page
 
ordnance1
post Feb 25 2018, 05:44 PM
Post#5



Posts: 567
Joined: 7-May 11



Thanks for your reply, but, I get an object required error
Go to the top of the page
 
ordnance1
post Feb 25 2018, 05:47 PM
Post#6



Posts: 567
Joined: 7-May 11



Thanks for your help, all is working now. I missed that the r was missing from st.open.
Go to the top of the page
 
DanielPineault
post Feb 25 2018, 06:01 PM
Post#7


UtterAccess VIP
Posts: 6,270
Joined: 30-June 11



thumbup.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2018 - 11:07 PM