473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Comparing two columns in one table

283 100+
Ok here is the situation. I have a form with two text boxes you can enter information in to. One is an ID number the other is a date. What I want to do is make it, for example todays date you enter in the same ID number twice it will error out. So basically one text box compares to another on the back end. they look and see if a certain ID number has already been entered for todays date, if yes then it gives and error message, if no contine.

Any help would be great!

thanks in advance!
Mar 30 '10 #1
4 6865
ADezii
8,834 Expert 8TB
The best way to handle this, in my opinion, is at the Table Level by making the combination of both Fields a Unique Index.
Mar 30 '10 #2
slenish
283 100+
Will that work for what im trying to do? From they way im looking at it I dont know if that will work.

What I need to do is compare one column to another. I want it so Column 1: IDNumber and Column 2: Date compare to each other. Plus it has be able to accept duplicates if you were to enter in a different date. So its reading the rows with the columns

for example;

*Brackets are to symbolize columns periods for space
[IDnumber]...............[Date]
1234.......................1/1/2010
1235.......................1/1/2010
1236.......................1/1/2010

then if i were to type in

1236.......................1/1/2010

it would error out plus the same ID number coule be used again on a different date

1236.....................1/2/2010

Im thinking an if statement to compare column1 to column2

I know this example is not right just demonstraiting possible answer


Value1 = IDNumber
Value2 = Date

If [table1].[Value1] & [table1].[Value2] = "Value1", "Value2" then
msg box = description
else
continue
Mar 31 '10 #3
ADezii
8,834 Expert 8TB
What I described would work, namely it would insure that the 'combination' of both Fields are 'Unique'.
Mar 31 '10 #4
slenish
283 100+
Ok I got a code to some what work but I can only get it to error out if you type in two number values. How do I adjust this to compare a number and a date instead of two numbers?

Expand|Select|Wrap|Line Numbers
  1. Private Function IsDuplicateRecord() As Boolean
  2.  
  3.     On Error Resume Next
  4.     Dim PreviousRecordID As Long
  5.  
  6.     IsDuplicateRecord = False
  7.  
  8.     PreviousRecordID = 0
  9.     PreviousRecordID = DLookup("MyID", "CustomerT", "MyID<>" & MyID & _
  10.         " AND CustomerID=" & CustomerID & " AND PersonID=" & PersonID)
  11.  
  12.     If PreviousRecordID <> 0 Then
  13.         MsgBox "Customer Exists Already"
  14.         IsDuplicateRecord = True
  15.     End If
  16.  
  17. End Function
  18.  
Mar 31 '10 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: ArunPrakash | last post by:
Hi, I have 2 tables and each has a text column. When i compared the 2 columns with the LIKE operator i found something strange. create table test1( var1 text ) create table test2( var1 text ) ...
12
by: NickName | last post by:
I have the following situation. One set of data has 274 rows (set2) and anther has 264 (set1). Both data sets are similar in structure as well as values for both of them were extracts from the...
2
by: gloria | last post by:
I would like to compare data across two tables. I have part information in a table. I get a new set of information periodically. I would like to compare my new info to my old info. I recognize...
6
by: teddysnips | last post by:
I have a table called WorkItem. It models a chunk of work done during a working day. It has two columns that I'm interested in: Start (smalldatetime) - the TIME the work block is begun...
19
by: Will Lastname | last post by:
In one of the applications that I'm working on I have 2 sets of functions that build different datasets. Imagine 4 columns in a datagrid. Inside those 4 columns I have nested datalists. Two of...
21
by: Kristaps | last post by:
Hi everyone! I have some questions, maybe someone can help me... I write script for table comparing, but it works wery slovly. There is the script, can anyone give some tip how can I make this...
10
by: saravanand | last post by:
Dear All, I am new to Ms Access and I am doing Product Hierarchy in SAP. I have to check for redundancy of datas from the SAP system and from another file. Difference found in code or description is...
0
by: saravanand | last post by:
Dear All, I am new to Ms Access and I am doing Product Hierarchy in SAP. I have to check for redundancy of datas from the SAP system and from another file. Difference found in code or description is...
8
by: irek.szczesniak | last post by:
Hi, I have table pairs that I need to compare, and produce another table that shows differences. I can't just open them in separate browser and look for differences, because I have many such...
2
by: sumuka | last post by:
Hello Everyone, I'm doing a project in VB 6.0 and i've a flexgrid which contains some numerical values in it's cell now i need to compare these values present in flexgrid with the values in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.