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: 541
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,490
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,068
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

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ordnance1
post Feb 25 2018, 05:40 PM
Post#4



Posts: 541
Joined: 7-May 11



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



Posts: 541
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: 541
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,068
Joined: 30-June 11



thumbup.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 04:30 AM