Solved

Next number in a field

Posted on 1998-06-22
8
321 Views
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.

Thanks.

P.D. I`m sorry for my poor english but I need your help.
0
Comment
Question by:aacosta
  • 4
  • 4
8 Comments
 
LVL 3

Accepted Solution

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

VALUES ('%date%

into
values (date(),
0
 
LVL 1

Author Comment

by:aacosta
ID: 1975627
The problem is in my correlative field, not in my date field.
0
 
LVL 3

Expert Comment

by:chapie
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:aacosta
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.
0
 
LVL 3

Expert Comment

by:chapie
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)

0
 
LVL 1

Author Comment

by:aacosta
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.

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.

0
 
LVL 1

Author Comment

by:aacosta
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)

Thanks!.
0
 
LVL 3

Expert Comment

by:chapie
ID: 1975633
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??
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

832 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