VBA Access SQL Server table insert behaves differently

Question

I have a problem when changing access code to SQL Server. I am trying to save the data in SQL Server, not in the Access database.

Access code

SELECT MAX("SPR-" & VAL(Replace([RequestID],"SPR-",""))+1) AS AutoID 
FROM Tb_Request;

SQL Server:

SELECT FORMAT(MAX(1 + REPLACE(RequestID, 'SPR-', '')),'SPR-#') AS RequestNo 
FROM dbo.PilotRequest

I think I changed correctly because I checked in SQL Server, the result is correct.

When I apply this code in Access, the result looks like this:

(There is data in the dbo.PilotRequest table, which is '5555'.)

Is there anyone who knows the reason for this?

you say that you have 5555 but your picture shows that you query 5556 — jsotola
– jsotola, Commented Sep 1, 2017 at 4:05
No, that's not. SPR-5556 is correct one because I made query like that. But in the Access program, I get '0PR-#' which needs to be like 'SPR-5556'. I do not know why this happens. — jangho yoon
– jangho yoon, Commented Sep 1, 2017 at 4:15
have a close look at the properties of the Request No combo box. maybe the wrong data is being displayed — jsotola
– jsotola, Commented Sep 1, 2017 at 4:24

Erik A · Accepted Answer · 2017-09-01 08:32:27Z

1

You need to do the concatenation outside of the Max, else your Max will compare strings and fail (because there's a string inside it).

SELECT "SPR-" & MAX(VAL(Replace([RequestID],"SPR-",""))+1) AS AutoID 
FROM Tb_Request;

answered Sep 1, 2017 at 8:24

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1