This is a lot like my last post, but not exactly...
I have a table with some really strange fields. They all have 4-8 digits, and those digits stand for the date--but not for simple mmddyy. This is much more complicated than that. Each 4 or 2-digit number set stands for something different.
is a sample of the numbers in the fields. Here is the way the website says to define them:
NOTE: The seventh and eighth columns of each document describe the initial and most recent coverage dates using the SICI standard, which defines standard month codes for seasonal and quarterly issues. (A full list of the month codes used for seasonal and quarterly issues appears below.) Dates may appear as a year (1989), a year and month (197601), or a year, month, and day (19331229). Sometimes the format for initial and most recent coverage dates may differ due to changes in publication frequency over the history of the journal. For example, coverage may start with an annual issue (1898) but finish with a monthly issue (199912) or coverage may start with a monthly issue (193002) but finish with a supplemental issue containing no corresponding month (1972).
Month Codes Used in JSTOR Delimited Lists
01=January02=February03=March04=April05=May06=June07=July08=August09=September10=October11=November12=December20=Winter (when it appears at the beginning of a year)21=Spring22=Summer23=Fall24=Winter (when it appears at the end of a year)25=Early Spring26=Late Spring27=Early Fall28=Late Fall29=Early Summer30=Late Summer31=1st quarter32=2nd quarter33=3rd quarter34=4th quarterSo, if it's a 6 digit number, that's going to be 4 for the year, and 2 for one of the codes above.
If it's an 8 digit number, it's going to be 4 for the year, 2 for the month, and 2 for the day.
If it's a 4 digit number, it's just 4 for the year.
There are 665 of these, and I'll need to update them each time I download a new version of the file. So.... can anyone help me? If I could get the numbers into separate cells (at least temporarily), I could do a find and replace and add my final results back together in one cell when it's finished. I would want something that would put the first 4 digits into a cell, then the first 2 digits of what's left into a cell, and then the last two digits of what's left into another cell. I don't mind running 3 different queries.
have a table call 'ClinicTimeTable' there is a column call 'ClinicDate', another column called 'DrID', different row could have same 'ClinicDate' and 'DrID', so I wanna to count how many Drs in a particular day, I have following code, but it acts so werid, it onlys work for some dates! say if I put 02/02/2006 then I can get the number of DrID by observing 'count', however, if I put 01/02/2006 like below, count is 0! but there is some records with 02/02/2006 (I open the table directly in Access), how can this happening? how can it only partly works? it will make more sense if it don't work at all.
Dataset ds = new Dataset(); query = "SELECT DISTINCT DrID FROM ClinicTimeTable WHERE ClinicDate = #01/02/2006#" ; OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, oleDbConnection1 ); myAdapter.Fill ( ds ); int count = dds.Tables[0].Rows.Count;
any helps will be so appreciated! I am going mad at this problem!
by the way, i just tested that if any day in Jan 2006 is fine, say if i search 23/02/2006 or 22/02/2006 is fine... can't imagine why...
I've been using Access 2002 since approx 2003 now. I won't upgrade as I've used 2007 and don't like the new interface.
I'm competent with it and regularly build locally used databases or databases for websites.
About 2 months ago my copy starting pissing me off. Everytime I click on any of the "create" links, it plays an alert sound. Anytime I click on an already created query/table/report etc it plays an alert sound. The only other time it happens is if I click on the button in the toolbar in windows to open a previously minimised window.
There seems to be no option to turn this off or on anywhere, so I'm completely stumped. It's not critical but it's bloody annoying. Anyone else had this and figure it out or anyone simply know how to stop it doing it?
I could obviously turn the speakers off but I like to listen to music whilst I work, and shouldn't have to either!
Thanks in advance
PS - My "Provide feedback with sounds" option is set to OFF
Hi, im currently under going a Driving school project database. I have the following tables, Client, Instructor, Lesson and Lesson Type. However i feel like i want to go into more depth, e.g. make tables like recording sections of theory and practical tests, etc.
Currently my database with 4 tables is in 3rd normal form.. but say if i had 10 tables linking via relationship in query, what normal form is this? Is this gd practice or bad practice? :confused::confused:
This has been going through my head lately and im wanting to get take alot into consideration for my project. This might be absurd question to say in the forum but hope anyone can help me and explanation for me.:o
I currently have two strings that look like this 070707 and 020709. What i need to do is compare these two dates and see which one is greater. Is there any easy way to do this since the year is just yy instead of yyyy?
I have a very simple (it has to be) database which holds names, address’s and telephone numbers (I told you). What I want to do is this when I come to the Town field, a drop down list is displayed with a few local town names (Peterborough, Huntingdon etc) and the respective STC code (01733, 01480 etc). When I select Peterborough in the town box the STC code 01733 is placed in the STD field automatically. I have two tables, one with the address’s (including Town and STC) field and the second one with two fields Town and STC. How do I get the two tables or forms to work together. Any help would be appreciated, many thanks.
Moderators - please help. Apparently there's a 70 message limit on saved Private Messages (PM's). I've long since exceeded the limit, but can't find a mechanism in this forum to delete unwanted PM's Where am I going wrong here?
I have many queries that all do the same thing. The problem is that they're connected to specific controls on a form, so I have to make a new query everytime I make a new form to perform the same task as the others. This would be eliminated if there was a way to use the criteria the same way you would in code, like Me!controlname. Is there anyway possible to make these queries more universal so that they can be used by many forms?
I have a form, with some controls, a subform and some command buttons. In the OnKeyDown Event of the subform, i put the following:
If Me.NewRecord And Keycode = 9 Then Me.Parent.cmdAddSong.SetFocus End If
However, as soon as i hit the tab key when i am in the new record of the continuous sub form, rather than it moves to cmdAddSong, the focus is set to the control after it (cmdDeleteSong). Similarly, if i try to set the focus, to the cmdDeleteSong, after i hit the tab key, the focus will move to a next control (cmdAddAlbums).
Any ideas why does the focus does not go to the control i specified in the Me.Parent.Control, but it goes to Me.Parent.Control + 1 ?
gosh, I can't believe I forgot simple thing. when I'm opening a form in Access it automatically goes to new/blank form, I'm try to make it go to first record instead and can't remember how I didin't before.
Hey all, I've been banging my head all day trying to find a method and have nothing. Perhaps someone could guide me towards the right path... Here's a generic simplification of what I have:
ID# - Friend 1 - Fred 1 - George 2 - Fred 3 - Bill 3 - Ray 3 - Scott
Here's what I'm hoping to end up with:
ID# - Friends 1 - Fred, George 2 - Fred 3 - Bill, Ray, Scott
or even this would get me there:
ID# - Friend1 - Friend2 - Friend3 1 - Fred - George 2 - Fred 3 - Bill - Ray - Scott
I'm dealing with thousands of id#'s and thousands of possible friend's names.
Any help out there? If I need to explain myself better, just ask and i'll write some more...
thanx for any help you can offer...
Jim
P.S. I'm only a lite-weight in programming. I've been trying with a thousand different query methods so far using temporary tables and anything else I could possibly imagine...
i wanted to be able to send an email with attachement for this i created an outlook object and i filled in all the parameters and it creates the message successfully with the attachement and everything, but when i press send instead of sending the email to the recpient it puts it in the sent items without sending it, unless i opened my outlook and looked into the sent emails box then it sends it, i dont know if i can do something in order for the email to be sent automatically because i dont know if the problem is the code or it s a sort of security thing for outlook if anyone has an idea....................... thanks
I have many queries in a DB. they work fine, but *sometimes* some of my queries are wiped - when i try to run them they say "query must contain at least one cell" or something..
when i check the query then, its empty!
I have saved out the SQL code into a word doc because this has happened a few times now.. so i just copy and paste the query back in - but this is very inconvenient! what is going on?
I have 2 unbound boxes in the footer of my continuous form. Both total up separate sets of fields using DSum and do it correctly. I have used Conditional Formatting on Total1 to make the numbers turn bold and red whenever Total1 is greater than Total2.
Here comes the weird part. It will always work whenever Total1 is greater than Total2, but sometimes when Total2 is bigger, Total1 is still red, even after I attempt to modify it and it requeries, or I go to a different record and come back. Any thoughts??
I have a weird issue going on...I'm running a "Find Duplicates" query on one of my tables, I'm only checking for duplicate values in 2 fields and outputting a third. now this runs fine, unless I use the total's button to group them. When I group them that access instance stops responding. The reason I'm doing this is the third column can have duplicate values, for my purposes it doesn't matter, but I need to be able to see it.
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says "Function is not available in expressions in tabel-level validation expresions" on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
I modified a form in an existing split database, by simply adding several fields. Now, I get various messages such as " not Access database " or if it opens, the following message when I try to close it: "Your last change may not have been changed because an internal buffer was locked by another user... "
Plus, when I do get out, I cannot delete the .ldb file which it says is open by Administrator. The thing is I ma only one to try this new database and I set no permisasions or... ?
Hi, I have a table with two fields: CustomerName and OrderNumber. Some order numbers have dashes, for example: 123-4, 123-5, 123-6 etc. I wrote a query to strip those dashes from these ordernumbers, but only for 3 specific customers: Mark, Mike and Jane. This is the query:
SELECT CustomerName, OrderNumber, IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0, IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*', CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)), CStr([OrderNumber])), CStr([OrderNumber])) AS NewOrderNumber FROM Test GROUP BY CustomerName, OrderNumber, IIf(InStr(1,[OrderNumber],"-")<>0 And InStr(1,[OrderNumber],"-B")=0, IIf([CustomerName] Like '*mark*' Or [CustomerName] Like '*mike*' Or [CustomerName] Like '*jane*', CStr(Mid([OrderNumber],1,InStr(1,[OrderNumber],"-")-1)), CStr([OrderNumber])), CStr([OrderNumber]));
This query works great when I run it from Access. However, if I run it from Visual Basic .NET, it does not strip any dashes for any customers. I am using the following connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C: est.mdb And the following query: SELECT * FROM [Strip] ORDER BY NewOrderNumber (Strip is the query name in Access)
i have .adp projects.I am facing a problem for which i dont find any reason.whenver i open 2 .adp projects they hang up.I have to kill the msaccess.exe
It okay if i m running 1 project but i have to logout to open the second project.I can not run both at the same time.
I am creating a switchboard to access a few data entry forms and to preview reports before printing. It seems that whenever I add a button to preview a certain form(the 4th button on the form, all others work fine), Access goes crazy. I get several errors, including:
"You have entered an expression that has an invalid reference to the propery MaxRecButton. "
and
"The Open Form action has been canceled"
Im also getting crashes to desktop and low memory errors.
Now I am getting "referenced memory" "memory could not be wrriten" errors? Is this an Access problem or a "need a computer upgrade" error?
Any clue why this might be happening for this form?
hi, got a weird question. Im working on a form atm (duh) I have a form that exists in more tables. Now you have 2 solutions: I can use a query and generate the form, or i can make various subforms.
what is the best option? or doesnt it make much diffrence?
I am creating a database. There are two main tables - Table 1 is created via a Make Table query, which pulls from an Oracle db, which is the db for our department's customer service system. Table 2 is populated via a form. Table 1 has personal customer information and Table 2 has order information. The two tables share a Customer ID. Table 2 contains data that was imported from an Excel sheet (what the users had been using up until this db was created). For this example, let's say each customer will only ever have one order so there will be a record to record match. Also, since Table 1 has to be manually refreshed using real-time data, it may lag behind or be ahead of Table 2.
Here's my problem: I want the user to see the customer's name when he/she types in the Customer ID. I got the DLookup to work for NEW records - that is, for records that exist in Table 1 but have not yet been entered into Table 2 - but it will NOT work for the 300+ records that I imported from Excel. For those records, the Name textbox remains blank or if I type in the Customer ID, gives me an error about entering duplicate values.
Furthermore, if Table 1 is lagging behind Table 2, I want the user to just go ahead and enter all the order information and when he/she goes back to update the order at a later date (after Table 1 has been refreshed), the name to just pop up.
Ok, a friend of my was on my laptop and he crashed the hardrive. I was able to get the database I have been working on off the computer but when I use my computer at work to try to open it, it opens but says "Function is not available in expressions in tabel-level validation expresions" on all the date-time functions. I can't save anything and my queries are not running because of this date/time thing. Anybody know what is going on?
