Trouble with Select statement in Loop (1 Viewer)

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Good Morning! I am trying to add sequential numbers to a table. If it does not exceed a number another table.

I am having trouble with the insert portion. this code works in a normal query in the access query gui.
SELECT Format(Date(),"yy") & "-" & Format([Closed_File],"0000") AS test;
I can't seem to get it to work in the code below.
Also if there is a better way to do this please let me know.

Thanks

Here is the code I have so far

Code:
Private Sub Closed_File_NumbersUpdate()
Dim db As Database
Dim sqlstr As String
Dim sqlstr1 As String
Dim Closed_File As Integer
Dim ClosedNumber As Integer
Dim test As String
Dim rs As Recordset
Dim rs1 As Recordset

Set db = CurrentDb

sqlstr = "SELECT Max(Val(Right([Closed_File_Numbers]![Closed_Numbers],4))) AS Closed_File1" & _
               " FROM Closed_File_Numbers " & _
                " WHERE (Left([Closed_File_Numbers]![Closed_Numbers],2))=Format(Date(),""yy""); "
Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)
Closed_File = rs!Closed_File1
Debug.Print Closed_File

sqlstr1 = "SELECT Max(Val(Right([ClosedNumbers]![ClosedNumbers],4))) AS ClosedNum" & _
" FROM ClosedNumbers " & _
" WHERE (Left([ClosedNumbers]![ClosedNumbers],2))=Format(Date(),""yy""); "
Set rs1 = db.OpenRecordset(sqlstr1)
ClosedNumber = rs1!ClosedNum
Debug.Print ClosedNumber

 Do

   Closed_File = Closed_File + 1
   db.Execute "INSERT INTO Closed_File_Numbers(ClosedNumbers)" & _
    " Select Format(Date(),""yy"")"&"-"&Format(Closed_File,0000) As test;"
    Loop While Closed_File < ClosedNumber
 

End Sub
 

plog

Banishment Pending
Local time
Today, 10:50
Joined
May 11, 2011
Messages
10,258
The best way is to structure your table correctly. Discrete pieces of data should be stored discretely--each piece in its own field. It seems you've created some sort of encoding system and jammed at least 2 pieces of data into the one [ClosedNumbers] field. That is incorrect.

If the last 4 characters represent a distinct value then it needs to go into its own field. If the first 2 characters represent another distinct value it needs to go into its own field. If any other values are in there they need their own field as well.

Then, when it comes time to increment a particular value by 1, you simply query that one field, get your value and do your math. No need for complicated decoding and encoding.
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
The best way is to structure your table correctly. Discrete pieces of data should be stored discretely--each piece in its own field. It seems you've created some sort of encoding system and jammed at least 2 pieces of data into the one [ClosedNumbers] field. That is incorrect.

If the last 4 characters represent a distinct value then it needs to go into its own field. If the first 2 characters represent another distinct value it needs to go into its own field. If any other values are in there they need their own field as well.

Then, when it comes time to increment a particular value by 1, you simply query that one field, get your value and do your math. No need for complicated decoding and encoding.
I agree however this data is coming from a third party system So I have to deal with what I get.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
What you posted as one line of code is not what you posted in the code snippet?
 

plog

Banishment Pending
Local time
Today, 10:50
Joined
May 11, 2011
Messages
10,258
Define "can't seem to get it to work". Error message? Just not inserting?


Debug.Print both of your sql strings and see what's in them. Then take them to a query object and see if they run. Then set the INSERT to a string and Debug.Print it as well.
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Define "can't seem to get it to work". Error message? Just not inserting?


Debug.Print both of your sql strings and see what's in them. Then take them to a query object and see if they run. Then set the INSERT to a string and Debug.Print it as well.
Thanks for the help!!

I changed the code to this while testing.

SQL:
 Do

   Closed_File = Closed_File + 1
  ' db.Execute "INSERT INTO Closed_File_Numbers(ClosedNumbers)" & _

    db.Execute "SELECT Format(Date(),""yy"") & " - " & Format([Closed_File],""0000"") AS test;"
    Loop While Closed_File < ClosedNumber


End Sub

I am getting a type mismatch error 13

Also I have printed both of my SQL string and they are working correctly I get 650 and 580 in them.
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
What you posted as one line of code is not what you posted in the code snippet?
Thanks, I will look at it again I have been making changes trying to get it to work.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
Try
Code:
db.Execute "SELECT " &  Format(Date(),"yy") & " - " & Format([Closed_File],"0000") & " AS test;"
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Try
Code:
db.Execute "SELECT " &  Format(Date(),"yy") & " - " & Format([Closed_File],"0000") & " AS test;"
Thanks but apparently you can't do a db.execute with a select query. I will modify it and try again.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
Perhaps not, but isn't that just meant to be the second part of the Insert SQL?
I was only concerned with getting the syntax correct.

FWIW I'd probably have two strings strInsert and strSelect and concatenate them together.
That way I can inspect each one with Debug.Print. ?
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Ok I am making progress but could use a little help

Code:
Dim year As String
Dim Closed_File As Integer
Dim Closednumfinal As String
Do
year = rs2!years
   Closed_File = Closed_File + 1
  Closednumfinal = year & "-" & Format([Closed_File], "0000")
  
  db.Execute "INSERT INTO Closed_File_Numbers(Closed_Numbers) VALUES (" & Closednumfinal & ");"
Debug.Print Closednumfinal
    Loop While Closed_File < ClosedNumber
 

End Sub

When you print.debug Closednumfinal it =20-0581 When I look in the table it only shows -561.
Thanks for your help
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
Which would be 20 minus 581?
You need to make it a string?
 

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Which would be 20 minus 581?
You need to make it a string?
Thank you can you give me a clue on how to do it? I have changed the declaration to strings. That did not seem to help
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
Try
Code:
 Closednumfinal = "'" & year & "-" & Format([Closed_File], "0000") & "'"
or
Code:
db.Execute "INSERT INTO Closed_File_Numbers(Closed_Numbers) VALUES ('" & Closednumfinal & "');"

Basically enclose in a single quote. Just experiment.

might need str(year) in the first code. As I said, just experiment.

HTH
 
  • Like
Reactions: why

why

Member
Local time
Today, 10:50
Joined
Sep 28, 2020
Messages
40
Try
Code:
Closednumfinal = "'" & year & "-" & Format([Closed_File], "0000") & "'"
or
Code:
db.Execute "INSERT INTO Closed_File_Numbers(Closed_Numbers) VALUES ('" & Closednumfinal & "');"

Basically enclose in a single quote. Just experiment.

might need str(year) in the first code. As I said, just experiment.

HTH
Thank you so much, the single quotes worked. When you have time can you explain it? Once again thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:50
Joined
Sep 21, 2011
Messages
7,478
Well TBH I did not expect Access to take it as a maths equation, but once recognising it had, then thought on how to stop that. By enclosing in single quotes we made it a string. As long as that formula would not have an embedded single quote, that would be fine, else use triple double quotes instead. Strings and numbers trip people up all the time. :(
In reality, it is recommended not to 'mush' data into one field, but to give the data parts their own separate fields and 'mush' them together when needed.

Good luck with the rest of it. :)
 

Users who are viewing this thread

Top Bottom