Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Next number in a field

Posted on 1998-06-22
8
Medium Priority
?
330 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
[X]
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
8 Comments
 
LVL 3

Accepted Solution

by:
chapie earned 900 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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