473,327 Members | 1,919 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,327 software developers and data experts.

How to count dates in a single field

First timer question here: In a customer data table, for each customer I have a field that includes dates in a single field. Example:
Name Dates
Steve 87,91,01,05,11,12
Bob 99, 03
Ann 01,05,06

In the criteria field of a query, I would like to be able to determine who has at least three dates in the field. Any advice?
May 6 '14 #1

✓ answered by jimatqsi

cdutcher, when you say "dates" do you mean "years"? I don't see any dates in your example data. It is helpful if you say precisely what you mean, rather than some other thing.

If the data is consistently formatted, you might be able to select based on the length of the data in the columns. Three 2-digit numbers + 2 commas is at least 8 characters, so your query could include
Expand|Select|Wrap|Line Numbers
  1. where len([Dates])>=8
Jim

7 1380
Seth Schrock
2,965 Expert 2GB
My advice would be to normalize your data (See Database Normalization and Table Structures) and then this will be a very simple problem using the DCount() function.
May 6 '14 #2
jimatqsi
1,271 Expert 1GB
cdutcher, when you say "dates" do you mean "years"? I don't see any dates in your example data. It is helpful if you say precisely what you mean, rather than some other thing.

If the data is consistently formatted, you might be able to select based on the length of the data in the columns. Three 2-digit numbers + 2 commas is at least 8 characters, so your query could include
Expand|Select|Wrap|Line Numbers
  1. where len([Dates])>=8
Jim
May 6 '14 #3
Hi Seth,

Unfortunately, I'm a bit of a noob regarding Access/data, and am hoping there's another way to achieve this. I'll look into normalizing it during my slow-summer months.
I had read elsewhere that I might be able to count the commas, but my syntax is erroring:
Length[dates] - Length(Replace([dates], "," , " "))
May 6 '14 #4
Hi Jim. My apologies, yes years (two digit years). The fields contain varying numbers of years, but there may be a way to calculate based upon the commas (eg, three or more years will include 2 or more commas).
May 6 '14 #5
jimatqsi
1,271 Expert 1GB
So the where clause I suggested should work just fine for you.
May 6 '14 #6
Light bulb above my head just went on. Thanks so much!
May 6 '14 #7
zmbd
5,501 Expert Mod 4TB
As an alternative (just incase spaces mess things up)
[Dates] = "87,91,01,05,11,12"
Find the position of the last comma
You basically have groups of three plus one group, so find the groups of three and add one, find out where the last comma is and then return the wholenumber of groups:
((instrrev("87,91,01,05,11,12",","))\3)+1
NOTE: use "\" NOT "/" where MOD() returns the remainder "\" returns the resultant.
Yes, replace "87,91,..." with Bob's [Dates] and it will return 2, etc...
I'll leave it to OP to figure out the actual formula for the calculated field.
(^_^)

-It is best practice when naming fields, tables, and files to avoid the use of anything other than alphanumeric characters and the underscore (spaces although allowed are problematic from a programing point of view and best avoided) and it is VERY importaint to avoid all reserved words and tokens:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access


Also, I've reset the best answer to the post with the actual text of the clause (^_^)
May 6 '14 #8

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

Similar topics

2
by: Jen F. | last post by:
I have inherited a medical database in which there are multiple values stored in a single field (ie. "Current Conditions" field might contain 1-20 different conditions, separated by comma (ie....
2
by: Alicia | last post by:
Hi, I have a simple question. I wanted to count a Column containing Dates and the word "Empty". This is what I am trying to do. Closed Dates ------------ 4/2/2004 4/7/2004 4/9/2004
3
by: Michael Curtis | last post by:
This is kicking my @#$%. I have been asked to do a performance report for our vendors. I need to compare and count dates. 1. Is called which is in a table named JOB DATES 2. Is called which...
3
by: Bjorn Sagbakken | last post by:
What is the best way to read single field values from a dataset? The dataset is populated with a customer record, showing well in a gridview. But I also want to read the single field values, like...
1
by: nathanwb | last post by:
I have a small app that allows users to send messages back and forth to each other. I have a place that shows there messages and they can click on a message to read. I have a field in the table...
1
by: GRUNT | last post by:
As a beginer,Im trying to COUNT values in a single field where they meet the critria below., Then SUM then altogether. Coursename,enddate1 and enddate2 are Request.QueryStrings form an .ASP web...
1
Jerry Maiapu
by: Jerry Maiapu | last post by:
Hi there please can someone tell me how to create a query that will group a particular field and count the records in that field and return itsrecord with the maximum count.. The fields are in a...
2
by: stormtrupr | last post by:
I have a Table(PA Phrases) consisting of phrases to be used in a purchase agreement, but not all phrases apply for every purchase agreement. I want to allow the sales rep to select the phrases...
5
by: Doicare | last post by:
Hi all I am facing a problem in storing an array of integers to an OLE object field in the database, the used code for this action is listed below: Sub Import_data() Dim oAccesss As...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.