I am trying to create custom "icons" using values retrieved from an access db. Right now, I'm pulling records that match the user's name(Fname) or "default", then assigning the returned values variables and plugging those variables in as the image, URL, and title of the "icon". The problem is that this setup will only retrieve the first record from the db that matches "Fname" or "default" and I want to create seperate "icons" for each record that matches "Fname". I can pull all matching records using the maketable(), but then I don't know how to format these as I have here to make additional "icons". My source code is included below:
Code: <% if Fname <> "" thenopencnset rs = Server.CreateObject("ADODB.RecordSet") SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'" Set rs = cn.Execute(sql, , adCmdText)ID = rs.fields(0)URL = rs.fields(2)Icon = rs.fields(3)Title = rs.fields(4)rs.closecloseCNend if%><a href="<%=URL%>" target="new"><img border="0" title="<%=Title%>" src="menupics/<%=Icon%>.gif"></a><br><font color="#FFFFFF" face="Arial" size="2"><%=Title%></font>
Any help anyone could provide on this would be greatly appreciated. Thanks in advance. -Chris Gordon
I have a field of user inputed data. The data are dates. The standardized way we'd like dates entered is DMMDDYYYY. The first D is inconsequential, it just needs to be present, whereas M means month, the D means day, and the Y means year.
I would like to filter my results to ONLY see the data that has been inputed correctly. Can you filter data in a query based on formatting, and if yes, how so?
(The title is somewhat misleading because I don't want to filter the results necessarily, I just want the incorrectly entered data to not even show up period)
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
Hello, i have a excel spreadsheet that has a column with a persons surname (comma) and firstname i.e Steve, Jones.
I export this spreasheet in Access, but would like to splitt the surname and first into separate columns in the table.. whats the easiest way to do this?? Thanks
I have to create a new table for some data I have from work. The data was imported within quotations though, so it won't let me copy and paste it untill the quotes are removed. I have over 3000 data values in each column, so I can't just go through and delete every quotation mark. Is there any way for me to select a column and remove the quotations around every value?
In a table, I would like a particular field to look thusly:
xx-xx-x-x-x
My question is how do I format it so that the dashes (-) are automatically inserted when the data is keyed into the input screen. The dashes would also have to be displayed on the table.
Hi all the problem i am facing is that the table exported by ACCESS to EXCEL is without any kind of formatting, bold text italics etc. , is there any way i can make access export the excel file with bold column headings and the cells having solid borders(basically any formating i wish to give). Each time i export the table i have to open excel and format the spreadsheet giving headings highlighting stuff and all and it is very troublesome so i did make a macro in EXCEL for it but i still need to open the file in Excel and run the macro. is there some way to run that macro during the export process so that the user just gets the file in the format he/she wishes. thanks for you help in advanc
I have a table in access and I need to format it so that when column "Status" = "Closed" column "Closed" = "yes". so it will automatically mark column "Closed" "yes" if "status" says "closed". Instead of having to change both columns all the time.I know how to do that in MS Excel, but is it possible in MS Access 2013?
I have been using Access for several years but always in a basic way. I have started work on an existing Members database where the Members Addresses have been pasted in from Notepad or been imported from csv or manual entry.The ones that have been manually entered are on one line. The ones that are imported from csv or pasted from Notepad are on multiple lines like you would naturally type on an envelope..In Notepad they look like this:
'Oakdale' 123 Expression Road Name of Town Name of County Postcode
Sometimes the addresses have 4 lines and sometime they also have the name of a village which makes it 5 or 6 lines. When imported into Access they also view (datasheet view) in the same way with the return at the end of each line. However, I cannot enter a new address in this manner directly into Access.How can I enter the address text into a text filed and be able to format it so that it looks like the above?
I have searched many times for an answer to this but cannot find a way of explaining it that search engines understand. I first came across this 12 years ago when I worked on a massive Members database for a radio station all the addresses where formatted in the same way and had been imported through csv. When I do this the csv file marks up the text as "'Oakdale' 123 Expression Road□Name of Town□Name of County□Postcode"
I have tried exporting to csv ad then importing it into GMAIL and this works fine I have also printed off labels and envelopes and all the addresses print fine too; I just don't know how to input it in that format other than pasting all addresses through a basic text file.
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast ) SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast FROM dbo_examInfo
I have a form which basically has - employee, work done, hours and a calculated field (w/hour) of work done/hours
I can manually set the conditional formatting however as time changes targets will go up and it would be easier to update the targets in another 'admin form' (i can manage that part)
Its just referencing the other table to say the likes of :
if work per hour is greater than tbltargets.convtargetmid then vborange, if work per hour is greater than tbltargets.convtargethigh then vbgreen else vbred.
I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.
These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.
Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.
Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.
Could anyone suggest a reason why writing an SQL command "create table" in visual basic would successfully create a table *but* change the way the data is stored in the fields?
Why then would the whole 20 spaces be used, the first four with the letters I entered, and the rest with 16 blank spaces I never did? If I highlight them and delete them, then rexamine the record, they are still there.
I'm doing a table for Future Business Leaders of America competitive events assignments.
I want the same table to hold Name and Event Name and be able to sort
However, I want the database to be able to sort to tell me each event that a person has, and at the same time, how many people are in each event. Presently, I have to switch between databases.
On one table, the event name is the primary key, and on the other the person's name is the primary key.
For the event name table, categories are "Event Name" , "Participant 1" , "Participant 2", "participant 3", etc.
For the Person's name table, Categories are "Name", "event 1" , "event 2", "event 3", etc.
I want to have ONE database that will sort lists for all participants in each event AND that will sort into a report or form so that I can inform participants of their events.
How can I do this?
I probably was not very clear, so ask lots of questions if you're unsure.
ok, i'm not the strongest SQL programmer, so i can't figure this out. i'm working on a report with the following source code:
SELECT vio_access_mid.safety_survey_date, vio_access_mid.violation, vio_access_mid.comments, vio_chemical_mid.violation AS chem_vio, vio_chemical_mid.comments AS chem_comm, vio_chemical_mid.safety_survey_date AS chem_date
FROM vio_access_mid INNER JOIN vio_chemical_mid ON vio_access_mid.lab_id = dbo.vio_chemical_mid.lab_id
WHERE EXISTS (SELECT ehs_labs.lab_id, tbl_list_building.building_desc, tbl_list_department.department_desc, ehs_labs.lab_room, ehs_list_lab_type.lab_type_desc, ehs_princ_inv.princ_inv_fname, ehs_princ_inv.princ_inv_lname FROM ehs_labs INNER JOIN tbl_list_building ON ehs_labs.building_id = tbl_list_building.building_id INNER JOIN tbl_list_department ON ehs_labs.department_id = tbl_list_department.department_id INNER JOIN ehs_list_lab_type ON ehs_labs.lab_type_id = ehs_list_lab_type.lab_type_id INNER JOIN ehs_princ_inv ON ehs_labs.lab_id = ehs_princ_inv.lab_id WHERE (ehs_labs.building_id = '0146') AND (ehs_labs.lab_room = '5678'))
AND (vio_access_mid.safety_survey_date = '6/1/2005') AND (vio_chemical_mid.safety_survey_date = '6/1/2005')
when i run the code, i get the correct amount of records (3) from vio_access_mid, but i also get 3 from vio_chemical_mid when in reality there are only 2 records. i will eventually add other vio_*_mid tables that will have a variety of results (some may have 0 while others might have 4 or 5, etc).
i would eventually like to be able to include the info from the WHERE EXISTS clause in my results, but one thing at a time...
anyone have suggestions on how to get the correct info from each vio_*_mid table?
I am writing a basic database to track performance for a stationery company.
Basically the database is used to track how long it takes certain depts to complete a task, so each dept ticks a box when they pass the task to the next dept and this populates a field with the date and time that they tick the box using the expression =IIf([Passed to Collate]="-1",Now()," ")
My problem is the box that you place the expression is also the box that you would select which column of the table the result would be sent to and I'm not sure how else you would run querys to establish total time and average time for the task.
I'm sure I'm missing something easy as I can't see why you wouldn't be able to save the result of an expression to the table and use it in querys/reports etc.
I'm sure this has been asked before, so I'm looking for search hints:
I would like to select query results displayed in a form, by check box, and add them to a table. The results displayed in the query are random and this poses a problem, at least for me anyway. :rolleyes: Any ideas?
Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
I design a form and all my work is based on that form. I have two tables which I join together as following. The unfitex field is calculating using fields from both tables as shown below.
Dim db As Database Dim rst As DAO.Recordset Dim sqlstr As String v = Combo37.Value sqlstr = "SELECT postdecgor.N1, postdecgor.Age, postdecgor.UNFITpc, postdecgor.DECENTpc, postdecgor.HHSRSpc, [UNFITpc]*[sample]/100 AS UNFITex, postdecgor.DECENTex, postdecgor.HHSRSex, [Sample size].[Age dwelling], [Sample size].sample FROM postdecgor LEFT JOIN [Sample size] ON postdecgor.Age=[Sample size].[Age dwelling]WHERE ((postdecgor.N1)='" & v & "'); " Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset) Set Form.Recordset = rst
I then set up some textbox which shows the results as following.
It works, I can see the results on the screen, however I would like to save the results into a table and I am not quite sure how to go about it. Can you help??
:confused: I have 3 identical fe be database running each with a table called cars that has identical form values just obviously different data. I want to create a table or query to display all the results from these but whenever i try and make a query i cannot seem to work it out. Anyone got any ideas? :confused:
Alright, i'm almost sure this isn't the best way of doing this,
But i wanna build a scoring system out of three criteria. cost, delivery, and qaulity. I have those tables built along with a contact table where there over all score will be tallied up. Each contact can have more than one entry in the criteria.
So what I did was i built a query to make a new table for each contact to generate the score and the contact id, then using that ID, i update it to the contact table using the UPDATE function...however wheni have 3 criteria, and 400 contacts..this obviously becomes painfully slow.
The question is, is there a way to directly pull the sql query results (summing the total of each contacts score for each criteria), then storing it into the contact table without making a new table in the process?
