I am trying to reformat a string of text so that it is easier to read on a report.Based on the text, it seems like the best thing to do is to insert a carriage return before a date/time stamp.Here is the data (it's a conversation log)...it is three unique conversation log entries:
04/13/2014 8:21PM - all work and no play is no fun - Bob Smith04/13/2014 8:21PM - this is a test of the emergency broadcast system - Bob Smith04/13/2014 8:20PM - log testing - Bob Smith
If the carriage return is before the date/time string, then all entries would look the same. If the carriage return is after the date/time string.A user had suggested a child table to break out the data into separate records, but I'm sad to say that I never worked with child tables. Any way for replacing 'Date/time stamp' with 'carriage return' & 'Date/time stamp"
I have a bound form which opens as a new record. If I accidently press carriage return by mistake whilst entering it goes to another new record, how do I stop this behaviour?
I only want the record to save with the click of a button.
e.g: InputText is 123, InputText2 is abc I wanted to display the text inside textbox of frmCalendar as: 123 abc instead of 123abc. how shall i use the vbCrLf?
How i can cancel the a carriage return and replace with a tab or stop it moving down my subform and make it move the the next field instead of going to a new line.
I am using the Keydown but don't know how to cancel it, it just bring an message but it stops it moving to a new record,
Code:
Private Sub Barcode_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then MsgBox "rtn pressed" End If End Sub End If
I am sending an email from a button, and it runs a word merge and then attaches that word document and two others two an email. All that works fine, but in the body of the email the carriage return is not working.
I've tried "vbCrLf", "Chr(13)" and "Chr(10)". but none of them are working in this email. I use vbCrLf all of the time in many other emails and it works fine.
Code:
Public Function SendEmailFinancial() Dim MessageBody, vAttach1, vAttach2, vAttach3, vTo Dim appOutLook As Outlook.Application Dim MailOutLook As Outlook.MailItem
[Code] ...
This is what the email is looking like when it comes out:
Project ID: 2013YG00229 Project Title: Toolbox Talks Construction Safety Guides and Mobile App Program Manager: Shana Peschek I am attaching three (3) forms that are needed in order for the State of Washington to process grant payments for your organization for the purposes of carrying out the activities outlined in your final approved application.
I have developed a private sub which automatically creates an email when a button is pushed.
The code is working well, but I need to insert Carriage Returns in the Body Text of the email just before the word "Please" where it appears in two places. Is this possible?
I import data through different files into a table, the data comes in different formats. I have a problem sometimes some records have double quotation marks and carriage returns. I know how to replace them as
Carriage Returns
Replace([FieldName],Chr(13) & Chr(10),""
Double Quotation Marks
Replace([FieldName],Chr(34),""
But if i want to use an iif statement because in the field there could be a Double Quotation Mark or Carriage return then it does not find the record
iif([FieldName] like Chr(34),Replace([FieldName],Chr(34),"",[FieldName])
Is there a good way to return an id that is created when I insert values into a database? So I have this: INSERT INTO assignedReview ( reviewUser, reviewedEmpuser) VALUES (@reviewUser, @reviewedEmpuser); And from that I want to get the reviewID that is generated (setup as a random autonumber) on the INSERT to pass back the user.
However, if I use a text box on a form, with "New Line in Field" set, and then in code use Sendkeys to post the address and the carriage returns as above, it does work.
The following code is in a text box on a report in a database that creates work contracts. The IIF statements and Dlookups pull the parts of the text together perfectly, but why the new line and carriage returns are completely ignored in the last section? Been working on this all day and we can't find a way of inserting 2 new lines!
Code:
=DLookUp("Description","QFRCL07") & (IIf(DLookUp("Description2","QFRCL07") Is Not Null,DLookUp("BasicSalary","Tmain","ContractID=ContractID") & " " & DLookUp("Description2","QFRCL07"),"")) & (IIf(DLookUp("Description3","QFRCL07") Is Not Null,DLookUp("Job","Tmain","ContractID=ContractID") & " " & DLookUp("Description3","QFRCL07"),"")) & (IIf([Forms]![FFRRCheck]![Text305] Is Not Null,(Chr(13) & Chr(10) & Chr(13) & Chr(10) & [Forms]![FFRRCheck]![Text305]),""))
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I am looking up a product based on a unique code in tblProducts...I need to return one of two values
If the value of [Product type] = "multi" then return "multi" else If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields
for example
tblProducts PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE C13T0714010 | single | <empty>| yellow | <empty> C13T0754010 | multi | red | yellow | blue
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
I am having an issue with a select query as I want one column to show the MAX and "00" value of the same Column. How do I go about having this task completed.
For example, the column has 00,01,02,03,04,05,*1,*2,*3,*4,*5 so I want a query that will return values of "00" and "05" on this example 00 05
Further example, this is what I have now
SELECT qryPR34_UNION.PR34001, qryPR34_UNION.PR34002, qryPR34_UNION.PR34003, qryPR34_UNION.PR34005, qryPR34_UNION.PR34006, qryPR34_UNION.PR34007, qryPR34_UNION.PR34013, qryPR34_UNION.PR34014, qryPR34_UNION.PR34098, qryPR34_UNION.PR34099, qryPR34_UNION.PR34100, qryPR34_UNION.PR34101 FROM qryPR34_UNION WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0") AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));
On the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005
i have a query with an inputbox for numbers [Enter Number]..if i want the query to return multiple numbers how would i do this? so the user could have 3 5 and 8 to search for, currently the [enter number] allows them to search for one at a time.
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C 123456 AAAA 1/8/13 123456 BBBBI 1/8/13 123456 CCCC 1/8/13 123456 DDDD 1/8/13 123456 EEEEEE 3/10/13 123456 FFFFFF 3/10/13 123456 GGGG 3/10/13 123456 HHHH 3/28/13 123456 IIII 3/28/13 123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.
I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?
I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.
Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.
E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography
I would expect: when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned when TBL_Email!Category = Maths : Students A,B to be returned when TBL_Email!Category = Physics,Geography : Students A,C to be returned
I have a large table with millions of entries. I want to run a query to return all entries that are on a Saturday. The date stored in the table though is just typical date format eg 15/11/2015.
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
I'm currently busy with something for my thesis as a student and I need to use Access for this. I'm not too new at access, I know how to do the very basics, let's say on the level of [if field contains *"text*", return x].
However I am struggling right now on something that shouldn't be too hard... I could do it immediately in Excel if there werent millions of rows..I have 2 tables. Table 1 regards a list of patent publication numbers (eg. WO2012024604A3) and additional data (publication date, title, etc), only the publication number matters for me now.
Now what I'd like to do is to create a third table which has for each of [Table 1].[Publication number]:
Column 2) A count of how many times the [Table 1].[Publication number] is found in [Table 2].[Cited Refs - Patent] ...
Column 3) In case a patent is cited more than once, return the [table 2].[publication Number Citing Patents] value of the earliest citing patent (so with the lowest Publication Date value).
For Column 2 I had expected it to be an easy count(iif( [Table 2].[Cited Refs - Patent] = "*"&[Table 1].[publication number]&"*")) command but apparently it's harder than that..
I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;
