how do I extract the number of dates and number of percentages out of a cell?
I have for example: "within 10 days 5% discount".
In this it is always "days" and "discount". So I would expect something like 4 digits left of "days" and something like: 3 digits left of "discount".
I'm trying to find a function in access that could do the following:
ZAB103-3012
ZAF405-HD-0001
Turn those cells into:
AB103
AF405-HD
Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it.
Hi experts,
I have a mainform in which there is a subform. Is there any way that I can retreive the value of the selected cell of the subform in the main form?
Currently what I usually do is that using a text box and setting it's control source to that special field in the subform, we have the value. But this is only for a specific field.
To explain my problem more precisely, I have a subform with 7 columns (fields) and 5 or 6 rows (datasheet view). When I click with the mouse on a specific cell ( or navigate within the subform with the arrow keys) I want to have the value of that cell in my main form.
Thanks in advance.
Hiya,
At present I am running a database which opens several queries and then I have to manually paste this informaiton into a spreadsheet.
What I want to know is how do i go about pasting this information into a specific spreadsheet and then into the correct cell.
I have had a look around and have not been able to find what I am looking for.
Cheers
Carl :confused:
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
Thanks
S
Hi everyone,
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) )
However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
Many thanks,
i have a table of articles. A field in the table is ArticleSubject
the ArticleID is made up of 3 letters then 3 numbers. i want the 3 letters to be something according to the subject
for example i want the first 3 letters of the ArticleID to be MAT*** (* is a number) if the subject is Maths
or ENG*** if the subject is English
the subject is picked from a listbox in the same record
how would i do this in a table . i am reluctant to use append or update queries.
but will do so if its the only way.
how to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
Hi,
I have recently been doing a lot of work on this area. Im able to export to where i want to and run macros through the VBA code inside of Access to edit the spreadsheets. This is ok if your making a new excel workbook/worksheet.
But what im stuck on is exporting to a so called template in excel. I can export to it at the moment but creating a new worksheet, in which i have to then cut and paste the data into the correct worksheets through code and then delete the worksheet that i had been working from (which is annoying because you have to confirm the deletion of this worksheet, which is why i couldnt really do the process this way).
What i want to know is there a specific way of telling the data you are exporting from a table/query/querydef to go into a certain worksheet and into a certain cell. For example; a list of names, i want all the Surnames to go into a worksheet called "Claim_Breakdown" and start from cell "A15" downwards until they have all been exported into the worksheet.
Anyone have any ideas on how i could achieve this? Thanks.
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedI am trying to figure out how to make a button that sends an email to a specific email address, containing the information from 1 row.
we have rows where we put the the address, the quantity and the time interval we can collect the packages in. these information have to be send to a trucking company. I want access to send an email to the trucking company's email address, and not a whole report of all the rows, but be able to choose to send row 1,2,3 etc.
so i have an interesting question and im hoping that someone can help on this one. i need to pull date from a specific table, no problem, that's written and working fine, next i need to be able to join the data from another table by a primary key, again no problem. third, i need to be able to select the date (using WHERE) for a specific date range. (i.e. i enter the date range of 01/7/2007 to 15/7/2007) and the query comes back only showing the data from that specific time, not the data from before or after. this is where my problem lies, all the entered data is being shown after entering my date range. i am going to include my SQL statement, just so you can actually see what im really talking about.
SELECT srealest.Name0, srealest.Dist1, SREpayments.Face2Pd, SREpayments.Penalty2Pd, SREpayments.[2paid], SREpayments.Face3Pd, SREpayments.Penalty3Pd, SREpayments.[3paid], SREpayments.Face4Pd, SREpayments.Penalty4Pd, SREpayments.[4paid], srealest.Map, srealest.Parcel, srealest.LeaseHold, srealest.TaxRebate1, srealest.TaxFace1, srealest.TaxPenalty1, srealest.TaxYear, srealest.BillNo, srealest.PdRebate1, srealest.PdFace1, srealest.PdPenalty1, srealest.DatePd
FROM SREpayments INNER JOIN srealest ON SREpayments.BillNo=srealest.BillNo
WHERE (((SREpayments.[2paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[3paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]) Or ((SREpayments.[4paid]) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date])) Or (((srealest.DatePd) Between Forms![SD SRE]![Beginning Date] And Forms![SD SRE]![Ending Date]))
ORDER BY srealest.Name0;
any thoughts or ideas on how to accomplish this would be greatly appreciated!
Hello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
Hello!
I have "ID" column and I want to create new column called "ID_new" where the value of each record should be "1000" plus the value of "ID".
For example:
First record, has "ID"=1, than I want "ID_new" to become = 1001
Second record, has "ID"=3, than I want "ID_new" to become = 1003
etc..
Any tips/hints?
Thank you in advance & best regards
noah
Hi Guys
Not sure what I should do here. I would like to have a cell populate with the value of a specific cell previously.
explain:
in excel, we can use formulas like this:
A1: 00:00 B1: 08:30
A2: 08:30 B2: 13:00
A3: 13:00 B3: 15:00
etc, etc
Notice that each cell in A is the same as the previous B Cell. So the formula for Cell A2 would be something like: =IF(B1>0,B1,"00:00")
and Cell B3 would be =IF(B2>0,B2,"00:00")
The fields I have in Access are:
TimeIn TimeOut
So I would need to get TimeIn to be the same as the previous timeout???
Not sure If I'm making any sense right now :)
I have a subform in Datasheet view. One field contains numbers. When entering data into the datasheet I wish to copy the number from the cell above and add 1. Can anybody help me with the code to do this?
View 5 Replies View RelatedThis is a very stupid question, I'm sure...
I want to get Access to find a cells that have a certain value and make them a particular colour so that they can easily be seen by database users. I could also make the font a different colour. I've tried getting the builder to do this in the query I run (for the column the fields appear in) but nothing seems to work. How and where do I enter formatting for colour / font colour based on data that is already in the cell... and what do I need to type?
Also would it be faster to get Access to do this before I run a query - so that the formatting is appllied to the whole table or would it be faster to do it during the query process?
Any help would be much appreciated.
I am not sure if this has been done in a table before. I have seen it in forms but not in tables.
Here is my problem.
I have created a database for a planespotters club. This database has the tables.
ac_logged
ac_maker
ac_model
country
airport
I am trying to display selected information in the ac_logged tbl that is related to other cells. For example: If someone selects boeing in the maker field they will only boeing models in the model field. Also, if someone enters Ireland in the country field only the airports for that country would be listed.
I don’t want to do this in a form but I could settle for a query if I have to.
I was just wondering if this could be done in a table as opposed to any other way.
Hope I have made myself clear on this one. :confused:
OK, here is my situation. I have an access database which is updated automatically every night with new data. My companies solution to this is each morning, the entire database is downloaded to my computer to be used by another application.
In one of the tables, there is a field that doesn't work for my purposes, so each day I have to manually open the table, and change the contents of that field for every record. For example, lets say I have the following table:
Field1 Field2 Field3 Field4
Rec1 Wigets Faucet Model1A
Rec2 Wigets Faucet Model1B
Rec3 Wigets Sink Model1A
Rec4 Wigets Sink Model1B
Rec5 Wigets Toilet Model1A
Each day I manually change Field2 so it reads "Total" and whatever is in Field2:
Field1 Field2 Field3 Field4
Rec1 Total Faucets Faucet Model1A
Rec2 Total Faucets Faucet Model1B
Rec3 Total Sinks Sink Model1A
Rec4 Total Sinks Sink Model1B
Rec5 Total Toilets Toilet Model1A
Each morning when I receive the new database download, they all go back to widgets.
Is there a macro or module I can create to automatically look at Field3, and then assign a new value to Field2 based on Field3's value?
Any help would be appreciated!!!!
Has anyone else had this problem?
I have a program that brings in the upper and lower specs to a excel spreadsheet and then brings in the values from lab tests.
The program then uses the specs to determine if the value is in or out of spec and colors the value if it is out of spec.
My problem is that in one cell the upper spec that is brought in is 2.7 and it shows that on the spreadsheet but when you click the cell the true value stored there is something like "2.66990153". Since a test value of 2.7 is greater than that it colors it when it is in fact in spec.
I have stepped through the code and the spec value pulled from the table is coming in correctly at 2.7.
Why would the excel sheet have a different value?
What's the best way of storing arrays of data within a cell? Or should I be creating fields to the size of the array.The size of the arrays are unknown at the moment.
View 7 Replies View RelatedOk, there is one main form and a subform which is linked directly to a table. A VBA routine checks entries and flags certain rows for errors. We want to highlight specific cells that are out of tolerance.
Been playing around with the Me.ActiveControl.BackColor = vbYellow
But if this can be done to a particular cell on a table, and if this can be done using VBA.
I would like to turn a cell red in a query - is this possible or can this only be done in a form or report. I have created a table with medical information and want the cell to turn red when blood pressure entry is greater than 140. Not sure what and where formula should be entered.
View 3 Replies View RelatedI have a few tables. One of it is a matching table between a table with people information and a table with event information. Now it want to do something like that:
The first column is filled with the event name (reference to the event table is done so i can choose from the drop down list) ...
The second column should contain the company name of the persons who visited that event. (also done)
The third column now should contain the visitor name. but the drop down should be filter so that only the people working for the company chosen in column two selected.
In my visitorstable the company of the peoples is referenced so i have to check, when the drop down opens, is the company written in column two also found in the column in my visitors table.
If I have the following value in a cell:
Joe <100,894> Doe
Is there a function in Access that will clear out the <100,894> leaving me with Joe Doe? To my understanding the Replace function only can replace certain characters. How can I delete everything in between the < > as well?
Thanks,
Paul
When i use MS Access help to search for my query i get this result:"Note: If you add a calculated field that includes one or more aggregate functions to a query in qhich you're calculating totals on all the records, you muct set the calculated filed's TOTAL cell to EXPRESSIONHow do i do this?????
View 1 Replies View Related