trying to convert SQL query to Access, don't know where to start

[timw2979]

I just started working for a company the other week and they threw this query onto my lap with an unrealistic deadline. My programming experience is primarily in VB6, so I am not completely familiar with coding Access queries.

I was given code that was written for SQL, and the programmer that came up with it is unfamiliar with Access, so I am stuck trying to decipher this. If anyone could help me out, I would greatly appreciate it, as I am thoroughly confused and don't know where to begin. Here is the programmer's original code:

while exists(select top 1 accNum from NoteFile where patindex('%' + char(10) + '%',dbtrref) > 0)
begin
    --Insert note into table
    insert into #TL19Note(AcctNumber, Note)
    select accNum, (case when patindex('%' + char(10) + '%',
dbtrref) <= 0 then dbtrref else left(dbtrref,patindex('%' + char(10) +
 '%',dbtrref)) end)
    from NoteFile
    
    --Delete note from Note String
    update NoteFile
    set dbtrref = right(dbtrref,len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) + '%',dbtrref))))
    where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) > 0
    
    --Delete rows with no more notes
    delete from NoteFile
    where len(dbtrref)-len(left(dbtrref,patindex('%' + char(10) +'%',dbtrref))) <= 0 or patindex('%' +char(10) + '%',dbtrref) <= 0
end

--Eliminates any that are CRLR or just spaces
delete from #TL19Note
where len(Note) < 10

--Update the date
update #TL19Note
set NoteDate =left(Note,patindex('% %',Note)-1),
Note = right(Note,len(Note)-patindex('% %',Note))

--No '@' means no time?
update #TL19Note
set NoteTime = '0000'
where left(Note,1) <> '@'

--delete the '@' - we know which ones have it  because NoteTime is null
update #TL19Note
set Note = right(Note,len(Note)-patindex('% %',Note))
where left(Note,1) = '@'

--Update NoteTime, take time out of note
update #TL19Note
set NoteTime = left(Note,2) + substring(note,4,2), 
Note = right(Note,len(Note)-patindex('% %',Note))

 

[WayneRyan]

Tim,

The InStr function can be used instead of patindex.
The Mid function can be used instead of Substring.

You don't need a loop, the following will insert all "modified"
notes from NoteFile:

DoCmd.RunSQL ("Insert into #TL19Note (AcctNumber, Note) " & _
              "Select accNum, " & _
                      Mid(dbtrref, 1, InStr(1, dbtrref, Chr(10)) - 1) & " " &
                      Mid(dbtrref, InStr(1, dbtrref, Chr(10)) + 1) " & _
              "From   NoteFile " & _
              "Where  InStr(1, dbtrref, Chr(10)) > 0"

Then you can:

DoCmd.RunSQL "Delete from NoteFile where InStr(1, dbtrref, Chr(10)) > 0"

Just some ideas to get you familiar with the new syntax.

Lunchtime is over. Will look in later.

Wayne

 

[timw2979]

thanks for the help Wayne. I will be working on this throughout the day today and will post up if I have any other questions.

 

[timw2979]

In this SQL code, what does the '% %' mean?

update #TL19NoteHold
set NoteDate =left(Note,InStr('% %',Note)-1),
Note = right(Note,len(Note)-InStr('% %',Note))

 

[stephen81]

I think %'s are used instead of *'s as wildcards in SQL, so you may need to replace the %'s with *'s in your query, although I've got a feeling you can tick a reference or change an option to enable you to use %'s but I don't really know. I'm sure someone on this forum will know more.

 

[WayneRyan]

Tim,

Stephen is right the "%" in SQL Server is the "*" in Access. I don't
think there's a way to change it though.

My hurried post yesterday was just to give you some syntax ideas.

The "While exists(Select Top 1) ..." syntax works for SQL server,
It could just as well have been a cursor. In Access, you'd have to
use a RecordSet.

I don't see the need for a loop though.

I haven't picked the code apart, but the it looks like the While
loop would only iterate once. The Insert/Update/Delete SQLs would
seem to remove all Char(10) records.

Since you seem to be progressing, I'll wait for some really specific
questions.

Wayne

 

[stephen81]

Stephen is right the "%" in SQL Server is the "*" in Access. I don't
think there's a way to change it though.

I think this might be what I remembered seeing.