not quite right

[Peter Bailey]

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

[Peter Bailey]

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

[Douglas J. Steele]

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

[Peter Bailey]

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

[Douglas J. Steele]

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