how to remove Unwanted vba code

[sspreyer]

Hi all
i have got ms access calender template database im try to modify to my needs and i have hit a barrier

i have a form which displays the monthly view calender as you click on a day it shows in text box below the calender information from fields in my main table "assets" also shows information from tblVisitType table but i would like remove this information

strSQL = "SELECT assets.Last, Assets.First, assets.Date, tblVisitType.Type, tblVisitType.Code, assets.Time " & _
         "FROM tblVisitType INNER JOIN assets ON tblVisitType.TypeID = assets.TypeID " & _
         "WHERE assets.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY assets.Time, assets.Last, assets.First;"

and

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, " & _
          "tblVisitType.Type, tblVisitType.Code, assets.Insurance FROM tblVisitType INNER JOIN assets ON " & _
          "tblVisitType.TypeID = assets.TypeID " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

my knowleage is not great i have try to remove parts but keep getting errors

all im trying to do it is remove anything related to tblvisttype table from the codes above

thank in advance you legends

shane

 

[Mihail]

If you are lucky (and I think that you will be ) do this:

Backup the DB before proceed !!!!

Start to design a new query in the DesignQuery window.
Do not add any table to this query.

Switch to SQL view
Copy here the strSQL string.
Switch to Design view.
Remove unnecessary tables / fields
Switch to SQL view
Copy the SQL and paste in your code

 

[CazB]

Try this?

strSQL = "SELECT assets.Last, Assets.First, assets.Date, assets.Time " & _
         "FROM assets " & _
         "WHERE assets.Date Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
         " ORDER BY assets.Time, assets.Last, assets.First;"

and the 2nd one...

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance " & _
          "FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

 

[sspreyer]

thanks you both for your help i have final got it to work thank you cazb your code work perfectly one more question if one anyone can answer it i have now add new colunm call cancelled highlighted in bold this is a yes and no box but shows in sql as true or false is there away to changing this to yes and no my code

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance, [B]assets.cancelled[/B] " & _
          "FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

thanks again

cheer

shane

 

[TJPoorman]

Replace the bolded part with this:

iif([assets].[cancelled]=True,"Yes","No")

 

[sspreyer]

hi Thanks T J poorman im getting compile error expected end of statement

with this code:

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance, [B]iif([assets].[cancelled]=True,"Yes","No") " & _[/B]
          "FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

thanks again to everyone

shane

 

[TJPoorman]

Try the changes in RED

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance, [B]iif([assets].[cancelled]=True,[COLOR="Red"]'[/COLOR]Yes[COLOR="red"]'[/COLOR],[COLOR="red"]'[/COLOR]No[COLOR="red"]'[/COLOR]) " & _[/B]
          "FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

 

[sspreyer]

Try the changes in RED

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance, [B]iif([assets].[cancelled]=True,[COLOR=red]'[/COLOR]Yes[COLOR=red]'[/COLOR],[COLOR=red]'[/COLOR]No[COLOR=red]'[/COLOR]) " & _[/B]
          "FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

Hi T J Poorman thanks , It now show's yes and no but has changed my column title from cancelled to expr1006 i so supose this is somthing to do with the way the expression is written

thanks for your time help with this

shane

 

[TJPoorman]

after the iff statement add "AS Cancelled" (no quotes)

 

[sspreyer]

hi thanks again T J Poorman some reason when i add AS cancelled i get no error but it cancels out all the sql data in the text box have no titles or data which is strange abit loss on where to go now here my code see if i may of done it wrong but I'm sure its right

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance,assets.changes, [COLOR=red]iif AS Cancelled(assets.cancelled =True,'Yes','No')" & _[/COLOR]
"FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"

Yet Again Thank you ever so much for your time

shane

 

[sspreyer]

Thanks for your help i finally got it thank you ever so much T J PoorMan for your help Legend!!!!!!

My Final Code For

strSQL2 = "SELECT assets.id, assets.Last, assets.First, assets.Date, assets.Time, assets.Insurance,assets.changes, [COLOR=red]iif(assets.cancelled =True,'Yes','No')AS Cancelled " & _[/COLOR]
"FROM assets " & _
          "WHERE assets.Date = " & ctlDayBlock.Tag & _
          " ORDER BY assets.Time, assets.Last, assets.First;"