asked on

Next number in a field

Hello experts :-)

I hope you can help me.
I have a field that is consecutive (1 2 3 4 5 ... n) regarding my date, I mean every date it starts again.
I am trying to generate a command that does it automatically, but I dont get it. I am trying many variant of this command:
--- cut here --
INSERT INTO trouble_ticket (date, correlative)
VALUES ('%date%','select max(correlative) from trouble_ticket' + 1)
--- cut here --
Correlative is the name of the field I am trying to increase automatically. date of course is the date.

This is the error I get:

[State=22005][Error=-3030][Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression.

What I need exactly is to get the max number in the correlative field increase it in one and if there is not any registry for the day create it.

Thanks.

P.D. I`m sorry for my poor english but I need your help.

ASKER CERTIFIED SOLUTION

chapie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aacosta

ASKER

The problem is in my correlative field, not in my date field.

chapie

did u try the proposed solution....??!!??

I've never seen the %'s around date before I've only seen it either with the () or without - just simply DATE

What version of Access r u using

if you're walking in a forest and see a wolf, someone yells, get out of the way, it might be because you don't see a tree about to fall on you, instead of the wolf

aacosta

ASKER

I am working with pages HTX and IDC I use % to specify that it is a variable. It works fine, and I save my date without any problem, as I said before the problem is in the other field.

chapie

well, then there may need to be a new approach made on this....when is this SQL statement being executed?? is it part of a procedure for a button_click ?? The reason I ask is because if you are using a procedure you could declare a variable and use that for the max value

instead of
NSERT INTO trouble_ticket (date, correlative)
VALUES ('%date%','select max(correlative) from trouble_ticket' + 1)

dim intMaxValue as integer, rsTroubleTickets as recordset

intMaxValue = currentdb.openrecordset("Select Max(correlative) from [trouble_Ticket];")
'if the above line doesn't work, trying adding a SET in front of it

NSERT INTO trouble_ticket (date, correlative)
VALUES ('%date%','intMaxValue' + 1)

aacosta

ASKER

Hello chapie. Yes, you are right it is part of a procedure for a button_click, however I think I can not declare variables into a IDC file :-(

After you click the button it executes this sql statement in the server. At the end of this message is paste my IDC file

I tried the command you told me and I get the next message:
---cut here--
[State=S1000][Error=-3035][Microsoft][ODBC Microsoft Access 97 Driver] Operation must use an updatable query.
---cut here---

I don`t know why it does not work. I thouht it was right.

IDC FILE:
----cut here--
Datasource: trouble_sivensa
Template: c:\inetpub\scripts\sivensa\subscrib.htx
SQLStatement:
+INSERT INTO trouble_ticket (date, correlative)
+VALUES ('%date%','select max(correlative) from trouble_ticket' + 1)
--cut here--

"+" Indicates new line.

aacosta

ASKER

Hello chapie, I solved my problem but a got a new one!

When I use this command it concatenates the correlative value and the number one. For example if I have in the correlative field the value 2 it brings me up 21, why?, I just want to add 1 to correlative, I want not concatenates anything

+INSERT INTO trouble_ticket (date,correlative)
+VALUES ('%date%','%correlative%' +1)

Thanks!.

chapie

the explanation is pretty simple enought, but I'm not sure of the solution...it's treating %correlative% as a text string, instead of a number (which is what I understand the %'s to mean), what happens if you left CORRELATIVE without the &'s and the apostrophes??