Search results

"Select distinct" returns an error: Run time error '3093' Order by clause tbl_failed_amplicons.sample_id conflicts with DISTINCT Thanks

Hello, I'm running the following queries to concatenate values from a table. The query is working fine, however, sometime it returns duplicate values that I would like to remove. I've tried using "select distinct" with SQL statement but it gave me an error. Could someone please suggest how to...

Hello, I'm trying to pass a value from a listbox to a filter used to filter a continuous form. Dim ALKvalue As Long ALKvalue = Me.lstRanges.Column(5, 1) Debug.Print ALKvalue 'prints out 0.2 However, I get an error when I build the filter criteria with the following statement. "Access...

Which is correct usage of Now() in a query: ' Dim todaysDate As Date todaysDate = Now SQL4 = "UPDATE tbl_Projects SET tbl_Projects.second_review_completed_date = #" & todaysDate & "#" or SQL4 = "UPDATE tbl_Projects SET tbl_Projects.second_review_completed_date = " &...

Hello, I would like to generate today's date and time and use it in update query. When I try Dim todaysDate As Date todaysDate = Date Debug.Print todaysDate it returns 9/14/2017 but I would like to return date and time and also use it in update query. Do I need any special formatting to get...

I've changed the field type to rich text and left the text box as plain text. The indentations are correct, however, <br> appears where the carriage return should be. Is there anything that I can do to hide <br>? I've tried changing the text-box to "Rich Text" and it worked for the existing...

Hello, I've transferred short paragraphs of text from Excel to Access (long text) field and displaying that field in a text-box on a form. The carriage returns (new line) are not visible in a text box, however, if I copy and paste the text into a word document the blank lines between the...

What do you suggest I do? populate two listboxes with the values and filter one listbox with another?

Hello, I have a linked table from SQL server with a “Memo” field that I need to link to a local Access table where the corresponding field is “short text”. The goal is to find unmatched values between the two fields. I’ve tried the unmatched query wizard but it does not let me link a memo...

It's basically two queries combined into one. very cool. thanks

Hello query gurus, I'm trying to display distinct rows with "gene" and "aa_change" fields next to the last "Second_Rev" field entry in table tbl_Variants. I've tried Group By and distinct so far but can't figure out how to display the "Second_Rev" field with the largest id only. The current...

I removed the left join [ID] between the tables and it wipe-out all the records with the same sample_id from tbl_CNVs. UPDATE tbl_CNVs, tbl_cnvs_qry SET tbl_CNVs.First_Rev = [tbl_cnvs_qry].[First_Rev], tbl_CNVs.Second_Rev = [tbl_cnvs_qry].[Second_Rev] WHERE...

I'm using tbl_cnv_qry as a temp table to attach to a form so the user is able to edit data on this form. After the form is closed I use update query to record the updated in the two fields.

Hello, I'm using the following Update query to update two fields form tbl_cnv_qry table to tbl_CNVs table, for records that have the same sample_id. UPDATE tbl_CNVs INNER JOIN tbl_cnvs_qry ON tbl_CNVs.ID = tbl_cnvs_qry.ID SET tbl_CNVs.First_Rev = [tbl_cnvs_qry].[First_Rev]...

Why the back-end can't contain queries? Is it going to cause problems? It seems to work well that way, Thanks

I was able to connect but my back-end has only tables and not the queries. Should I import the desired queries into the back-end? I've tried to reference a copy of the front-end that has the queries but it produced an error message that the file was already opened exclusively by another user...

Hello, I wrote vba code in Excel that retrieves data from Access db. It was working well until I split and password encrypted the database. The back-end is on the network. So now when I tried to import data, I get an error message "Run time Error 3031. Not a valid password" I tried changing...

Hi, I've created a cross-tab query and was manually adding a chart to a form. Each time Access crashed "error has occurred please restart the program." I haven't tried with vba yet. Thanks

Hello, Is it possible to create a scattered plot from a cross-tab query in Access 2016? I get an error message every time I try. It works well if I export data to Excel and create a chart there but I would like to do this in Access. Thanks

I've tried: SumOfAF: IIf(IsNull(Sum([graph_variant_final].[AF])),"0",[graph_variant_final].[AF]) but it didn't work. Please demonstrate how to force the value to 0 in crosstab. Thanks