473,898 Members | 4,033 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Checking for Duplicate Records

2 New Member
I am trying to check for duplicate address records in a table before running an append query to add the user-entered address on a form. I am defining a string to capture the street number, name, suffix and city and then using DLookup to search the table (tblLocation). The problem is that the code keeps returning a data type mismatch in criteria expression error. I know it has to do with the street number field because the code works fine when it is omitted. The street number field type is Number with a field size of Double. is there a way to format or convert the string so it will be accepted?
Expand|Select|Wrap|Line Numbers
  1. Private Function DupLocation() As Boolean
  2.   Dim strWhere As String
  4.   DupLocation = False
  5.   If IsNull(Me!txtStreetNumber) _
  6.   Or IsNull(Me!txtStreetName) _
  7.   Or IsNull(Me!txtSuffix) _
  8.   Or IsNull(Me!txtCity) Then Exit Function
  9.   strWhere = "(([Street]='" & Me!txtStreetName & "') AND " & _
  10.              "([Suffix]='" & Me!txtSuffix & "') AND " & _
  11.              "([City]='" & Me!txtCity & "') AND " & _
  12.              "([Number]='" & Me!txtStreetNumber & "'))"
  13.   If Not IsNull(DLookup("[Street]", _
  14.                         "[tblLocation]", _
  15.                         strWhere)) Then
  16.     DupLocation = True
  17.     Call MsgBox("This location already exists in the database." & vbCrLf & "Please select it from the 'Trespass Location' dropdown", vbExclamation, "Duplicate Location")
  18.    Else
  19.     Call MsgBox("This is a new location", vbInformation, "New Location")
2 Weeks Ago #1
3 94
32,584 Recognized Expert Moderator MVP
Just caught this on the way out for the evening. Immediately I see a problem. Your number value is presented in the SQL as a string by adding quotes (') around it. Get rid of those & see if that's all there is wrong with it. I'll check back in later when I get back home this evening.

For further tips on working with SQL in VBA see How to Debug SQL String
2 Weeks Ago #2
2 New Member
Well, I feel pretty dumb right now. That fixed the issue! Thanks.
2 Weeks Ago #3
32,584 Recognized Expert Moderator MVP
Why feel dumb when you're simply on a journey as we all are?

If you were repeating the same mistake over & over again then you'd have a right to feel dumb. So far, no cause.

Best of luck & I hope I don't need to say it but please come back & post if you get into any other difficulties down the road.

PS. I just reread the last part of my linked article where I sound a little impatient of people who make mistakes. I was less experienced when I wrote that. It's targetted at those who need very strong hints in order to understand why it makes better sense to post the result as well as the code that creates it. I'm developing patience but you understood straight off so no need to feel it pertains to you.
2 Weeks Ago #4

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

Similar topics

by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since records to be imported can have duplicate records of the composite key I need to clean all but one of the...
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which contain same information in the ID field and the account number field once a week. Thanks in advance! KT
by: Andrew Chanter | last post by:
I have been working with an A97 database that performs a data processing function. It imports data from a flat text file then uses a dao transaction that executes a number of sql statements (about 30 in all) to append to various tables, delete records, copy from table A to table B etc, ultimately downloading to a series of text files that contain 'processed data'. The issue is that on the odd occasion (this process is executed every...
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to keep both records (or it could be more than 2 as well) where duplicate records are found. Also, I am interested in selecting all columns from the duplicate records. Thanks, Carroll Rinehart
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta, elcbtripselect.ELCBTRIP_starter_size, elcbtripselect.ELCBTRIP_UnitFunction, elcbtripselect.ELCBTRIP_strcb_speedi_frame_ty,...
by: Thomas Arthur Seidel | last post by:
Hello to all, I have a small or big problem with a customer data base, where during a change of system we might have created duplicate records. This should be easy to find, you might think, but, we are talking about roughly 10000 records or less in a total volume of 1 MIO records or more. I have considered a strategy: The station ID and a field with something like a sequence number are supposed to be unique during that period. The...
by: nomvula | last post by:
hi guys i need some help to duplicate records on my form datasheet: here's the example of my form results: ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual UJ 18-Apr-08 01-Mar-08 Fees: Asset 1 R 31,200.00 R 31,200.00 NMBM 22-Apr-08 23-Mar-08 P-MI (E) 07/2006 3 R 47,485 R 38,849 i have 200 records deplayed in the form i'm using access2007 and i have a command button which is the built in command button to...
by: Dilip1983 | last post by:
Hi All, I want to delete duplicate records from a large table. There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state. First of all when i tried to rebuild index it showed error as unique key violation. So i want to delete duplicate records for col1,col2,col3,col4 combination. How can i delete the duplicate records from this large table?
by: Sandhya1988 | last post by:
Hello friends, Can anyone help on my issue i have attached the screenshot with explanation Brand, Model & Item fields are text fields and Items Description is calculated filed ( & " " & & " " & ). So in Brand, Model & Item fields is need to allow duplicate records because same Brand is repeated with different models & different Items and same Model is repeated with different brands. And sometimes user using only Brand without Model & Item...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.