Hi all,
I am relatively new to access and need some help.
I have table with ranges of postcodes (NL) and regions. For instance
Table_Regions_Range:
Amsterdam 1000-1119
Haarlem 2000-2099
etc
And i have a records of schools:
Table_Schools:
SchoolA 1050
SchoolB 2050
etc
I want to query the data base and lookup in wich regio a school is based.
Where do I begin?
Thanx in advanced
Jan
I need to know how to be able to make my database accept only postcode format entries like RT24 6QP (english). Like a validation , it would only accept that format. How would i go about doing that.
Please explain to be in easy to understand contex as im no expert with access.
Thanks
I need a form to set up records
The table is something like
AutoID, Sequence , Low, High
Data would be
1, 1, 1 499
2,2,500,999
3,3,1000,9999
4,4,10000,999999
etc
Ho do I go about ensuring my sequences are correct
ie I wouldn't be able to add a record - 5,5,100,200
or if a user was to delete a record the user would be informed the sequences were out?
Thanks for any assistance.
Hi, I am inputing addresses into a massive databse, through a form, does anyone know a way, that i can get it to do a search for the address based on the postcode, like how googlemaps does it or something?
Thanks
Alex
I was wondering if anyone had any experience of integrating a product like QuickAddress (QAS) into an Access Database to aid the completion of address details. ThanksRussG
View 6 Replies View RelatedHi Guys,
I have searched the forum, and not really found the answer I`m looking for.
I used in my Database, a postcode field, which for 90% of the postcodes I input is is fine.
However, som of the postcodes dont conform to standard post codes.
I`ve found some info in this link, http://www.access-programmers.co.uk/forums/showthread.php?t=89393&highlight=links
but this didnt really help.
Most postcodes ( as set in the database properties ) confirm to thiis standard
SK01 1AA
Which if intered in this format works all OK.
However, some postcodes go in as fiollows
M01 1AA
Which leaves me a digit short, and messes up my search sections of the database, and the link to external map programs like M$ Mappoint.
Is there anyway that anyone knows of that will sort this out, and allow both entries withouht screwing up other programs.
This field is also a REQUIRED field, and must be a data field, or my external program links like M$ Map Point wont work.
THanks in advance guys.
Max
hello all
this thing is really bugging me im trying to come up with a post code validation and i have tried dozens, which should have worked really, but just havent so wondering if you guys knew any
examples of postcodes would be
BN3 8JH
BN41 8JH
theyre the only two types that are bound to be entered really
the first and second characters have to be a letters
the 3rd has to be a number
the 4th is sometimes a letter (not always present)
then theres a space
then there has to be a number
letter
letter
any suggestive codes would be great, as none of mine have worked its in access
thanks
nightmare lol
I run various queries on a daily basis for multiple date ranges (previous day, week to date, month to date, and year to date). The below criteria allows me to get WTD numbers but it has a flaw. When I run the reports on Monday, this criteria gives me Sunday and Monday's data when I actually want the previous week's data. Does anyone know of an iif statement that basically, says, "If today is Monday, give me last week's data, else give me wtd of the current week?" Right now, I manually change the criteria on Monday morning to hard code the dates and then revert to the formula on Tuesdays but I need to have this completly automated because there are a large number of queries and I don't have time to change them all. Thanks for the help!
Between Date()-Weekday(Date()-1) And Date()-1
I received help on previous threads. Basically, I have built a query to group by course title and then select the number of students that were assigned a course during a specifc time frame. Is there a way to show multiple columns in the query. For example my query now shows, enrollment for March, I would like to column next to it to show enrollment for Feb and then another column for Jan.
This is my current sql view.
PHP Code: SELECT tblContent.Title, Count(*) AS [Students Assigned]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Robins") AND ((tblContent.Code) Like "NH*") AND ((tblProfile.Groups) Not Like "*Professional Development Center*" And (tblProfile.Groups)<>"All Users,Professional Development Center") AND ((tblProfile.Delete)<>Yes) AND ((tblContent.Type)="Learning Track"))
GROUP BY tblContent.Title, tblContent.[Date Assigned]
HAVING (((tblContent.[Date Assigned])>=#3/01/2004# And (tblContent.[Date Assigned])<=#3/31/2004#));
Thank you in advance for any help you can offer.
Hi all
I have a bunch of student test scores. The test is scored out of 100. I want to COUNT the scores (ie I want to know how many kids scored 50 etc...). This is easy enough except the crosstab query will be very wide ie 100 columns.
I cant seem to reduce the size by grouping them into ranges (ie count the number of kids that scored between 50 and 60 in a cross tab. I've tried variations with select queries but without luck.
Maybe there is a way to code it.. and then show it on a report??
Any ideas?? Thanks
I have a table with employee names and there start time and stop time for their shift. There is also a start time and stop time which they cant work during their shift. So I have shiftstarttime and shiftstoptime and codestarttime and codestop time. I have to create a table with the employee names and generate a time called outboundtime. This outboundtime starts at a specified time (e.g. 9am) and runs for a specified time (60mins). This outboundtime has to be within the range of the employees shift and cant be within the range of the cant work time. If it was, then it would start after the codestoptime for that employee.
I have been trying "if then statements" but I can't seem to compare a range of time to another range of time. Only a specified time to a range which is making things confusing.
Hi,
I have a postcode field on a form that allows 8 characters. e.g. ST10 8BY including the space in the middle.
The only validation I have been able to use thus far is the above but I would like to know how to validate two letters (AA) then between 1 and 2 numbers (11) etc.
Is there any code/built in functions that allows this. I know there is an input mask but if I use that, I can set the poscode format up like ST10 8BY but it thinks that the rule has been broken if the postcode was changed to S10 8BY by removing the T. This is also a valid postcode.
Does anyone have any suggestions?
Thanks
hi guys,
Just been asked this and not sure if it is possible...
they do not want to pay loads of money to the royal mail for their postcode/address data. but they want a faster way (like typing postcode and address options popup) of entering addresses. There are tons of websites that can be used to find addresses with a postcode, i was wondering if it was possible to somehow link the database to lookup address from a website...?
anyone got any ideas?
thanks, james
Am trying to query a customer database to find a how many instances of each postcode exist to determine geographic distribution. Only interested in the first part of the postcode so the following code was suggested:
SELECT Count(owners.Field8) AS Total, Left$([Field8], Len([Field8])-3) AS Code
FROM owners;
However it returned a message saying "you tried to execute a query that does not include the specified expression 'Left$([Field8], Len([Field8])-3)' as part of an aggregate funtion.
What does this mean? :confused:
Hi All,
Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be mich appreciated.
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.PostCode;
I've tried to steal an SQL Postcode validator (This is for UK postcodes) and add it to an AfterUpdate event on a field in MS Access. It doesn't work, obviously,The VBA code is:
Private Sub Postal_Code_AfterUpdate()
Dim CHECK_Code As Integer
Dim LResponse As Integer
If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then
CHECK_Code = 0
[code]....
Also, how would I put the Check_Code value into the Message box to see what it's bringing back?
Hello,
Am posting this message (also on reports forum) as i dont know whether the problem is at report or query level. Hope you can help.
Hello all, this is probably simple but im new to all this!
I am trying to produce reports that show details for only the past year, at present my reports are bringing up details of all the previous years also. Is there any way you can put conditions on reports ( or should it be in a query) that tells the report to only include dates i.e. today - 365 days. At present i have to change the dates every day both a start and end date. Is this possible?
Thanks
I am attempting to create a parameter query to show a specific range of dates of service for my customers. Example: I want to see data from my table from Jan. 1st, 2006 to Feb. 15th, 2006 for any customers serviced in that time frame. In the query I have under my Date field, I have the following in the criteria field: Between [Input Start Date] And [Input End Date]. When I go to the while in the design mode of creating the query on the main menu under Query then to parameters and get my Query Parameters what should be put into the Parameter column and the data type columns and will this allow the user to input any start date and end date and get the information from the master table?
I also need to create a parameter query that will allow viewing of only each months table data. Example: Jan. 1st, 2006 through Jan. 31st, 2006.
Thanks in advance!
I must be having a bad day, as I can't get something really simple to work. I have a query, that amoungst other things, I want to select all records between two dates. For instance, the table has a 'ScheduledDate' field, and I have set up two parameters in the query that prompt for 'From' and 'To' parameters.
In design view, I have tried the following in the 'criteria' box for the 'ScheduledDate' field;
>=[From] <=[To]
This doesn't work ~ can anyone point me in the right direction. I've searched the forum, perhaps not using the correct keywords?
All help appreciated.
Calculating dates between TWO date ranges:
I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.
Example:
test table:
Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith
Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?
(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)
Don
I have a form that is similar to a post card. The fields are linked to a master table that contains all the customer data such as dates of service, street address, PO Box, City, State and zip. What I need to do is have a method to pull into the post card form a range of dates, so the form would have only January customers in it. I have parameter query that pulls ranges but it comes up in table form. I need the range to come into the fields on the post card so I can then print just the month of January.
Do I need to create a button on the form for the ranges and if so how do I get it to pull just one month's customer data into the fields on the post card form or can this be done?
Thanks in advance for the help.
I am trying to put together a quick database for my HR team. I need to be able to pull multiple date ranges to show which employees are up for an annual, 6 month and 90 day review.
Is it even possible to put multiple date ranges in one query, or is this going to get complicated? I have no real database background, and know absolutely no SQL so I'm flyin by the seat of my pants here...
I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?
View 7 Replies View RelatedHello all!
I have a customer database and I basically want to find out their geographical distribution. To do this I need to run a query that gives me the sum of customers for each postcode, but this is dependent on only the first few characters of the postcodes (or prefix), eg BH3. The length of the prefix varies between 2 characters and 4 characters with one or two characters followed by one or two numbers.
What I don't want to happen for example is to have postcodes counted as BH1 when in fact they are BH13 or to have postcodes coutned as BH13 when they are actually BH1 3LV.
Does anyone have any suggestions of how I can do this? :confused:
Any help most gratefully received!
:)
Hi
If i wanted to run a query from customer records in my database and wanted a list of specific postcodes that matched the criteria how would i do this?
For example each customer records has a postcode BD4 4KL, LS9 7YH ETC
I want to categorise each postcode set to see for example all the customers with postcodes begining with have ordered factsheets.
In the criteria part of the query how would i write it so it only extracts specific postcodes from the database from the ones i require
e.g "HU1, HU2, HU3 HU4" - checks database - returns all postcodes beginning with this.
Please help me!
Thanks
The problem with UK postcodes is that there are 6 different formats. These are:
A9 9AA
A99 9AA
AA9 9AA
AA99 9AA
A9A 9AA
AA9A 9AA
I've seen on similar threads where people have just forced the field into capitals, but my data requires that it is a valid postcode and I cant find an input mask that allows all UK postcode.