By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,955 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,955 IT Pros & Developers. It's quick & easy.

not quite right

P: n/a
The following code correctly creates the string and if I paste this into a
query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set querydefs
part of the code doesnt update the sql so I get the string from when it ran
previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click()

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DOSMBK.Date," & """d/m/yy" & """))" & " AS
DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Column(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Column(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
My apologies AGAIN i thought I had tagged this to my original question! of
yesterday.

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.co.uk...
The following code correctly creates the string and if I paste this into a
query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set querydefs
part of the code doesnt update the sql so I get the string from when it ran previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click()

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DOSMBK.Date," & """d/m/yy" & """))" & " AS DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Column(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Column(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub

Nov 13 '05 #2

P: n/a
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
qdfTemp = strSQL
End If
qdfTemp.Execute
Set qdf = Nothing
Set db = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.co.uk...
The following code correctly creates the string and if I paste this into a
query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set querydefs
part of the code doesnt update the sql so I get the string from when it ran previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click()

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DOSMBK.Date," & """d/m/yy" & """))" & " AS DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Column(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Column(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub

Nov 13 '05 #3

P: n/a
Interestingly it comes up with an error on the qdfTemp = strSQL part I thing
it said it couldnt evaluate.

:(

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:lG********************@twister01.bloor.is.net .cable.rogers.com...
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL) Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
*******************qdfTemp = strSQL***********************
End If
qdfTemp.Execute
Set qdf = Nothing
Set db = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.co.uk...
The following code correctly creates the string and if I paste this into a query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set querydefs
part of the code doesnt update the sql so I get the string from when it

ran
previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click()

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DOSMBK.Date," & """d/m/yy" & """))" & "

AS
DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Column(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Column(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL) DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub


Nov 13 '05 #4

P: n/a
Sorry: my fault.

That should be qdfTemp.SQL = strSQL

As well, I just realized that using qdfTemp.Execute isn't going to work, as
you've only got a SELECT query (the Execute method only works with Action
queries). However, once you're saved the SQL in the new query,
DoCmd.OpenQuery "Qrygetdosmodulebookings" should work.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg3.svr.pol.co.uk...
Interestingly it comes up with an error on the qdfTemp = strSQL part I thing it said it couldnt evaluate.

:(

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:lG********************@twister01.bloor.is.net .cable.rogers.com...
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings", strSQL)
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
*******************qdfTemp = strSQL***********************
End If
qdfTemp.Execute
Set qdf = Nothing
Set db = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@news7.svr.pol.co.uk...
The following code correctly creates the string and if I paste this into a
query it works everytime. It doesnt work though with the query Exists
function in as much that if the query is already there the set
querydefs part of the code doesnt update the sql so I get the string from when
it ran
previously.

If I delete the query everytime then the createquerydef part works.

I am not sure why. regards in advance

peter

vba follows:

Private Sub Command4_Click()

Dim db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String

Dim varItems As Variant
Dim strSelect As String
Dim strWhere As String
Dim strMods As String
Set db = CurrentDb
strSelect = "SELECT CDate(Format(DOSMBK.Date," & """d/m/yy" & """))" &
" AS
DateDisp, DOSMBK.[DOSM-DSName]" & " FROM DOSMBK"

For Each varItems In Me.ListDOS.ItemsSelected

If strMods = vbNullString Then
strMods = "=" & """" & Me.ListDOS.Column(1, varItems) & """"
Else
strMods = strMods & " Or (DOSMBK.[DOSM-DSName])=" & """" &
Me.ListDOS.Column(1, varItems) & """"
End If

Next varItems

strWhere = " WHERE (((DOSMBK.[DOSM-DSName])" & (strMods) & "));"
strSQL = strSelect & strWhere
If Not QueryExists("Qrygetdosmodulebookings") Then
Set qdfTemp = db.CreateQueryDef("Qrygetdosmodulebookings",

strSQL) DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
Else
Set qdfTemp = db.QueryDefs("Qrygetdosmodulebookings")
DoCmd.OpenQuery "Qrygetdosmodulebookings"
Set qdf = Nothing
Set db = Nothing
End If

'On Error Resume Next

'Debug.Print strSQL

End Sub



Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.