Next number in a field

Posted on 1998-06-22
Last Modified: 2008-03-17
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.


P.D. I`m sorry for my poor english but I need your help.
Question by:aacosta
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Accepted Solution

chapie earned 300 total points
ID: 1975626
try changing the following portion

VALUES ('%date%

values (date(),

Author Comment

ID: 1975627
The problem is in my correlative field, not in my date field.

Expert Comment

ID: 1975628
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 1975629
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.

Expert Comment

ID: 1975630
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)


Author Comment

ID: 1975631
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.

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

"+" Indicates new line.


Author Comment

ID: 1975632
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)


Expert Comment

ID: 1975633
the explanation is pretty simple enought, but I'm not sure of the'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??

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question