Solved

Next number in a field

Posted on 1998-06-22
8
316 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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